How to use Sum with subquery

Asked

Viewed 6,454 times

3

How can I use the sum with a subquery inside? I tried many ways, but I couldn’t.

When I try to put the sum shortly after the select main, message appears that I cannot use aggregation function with aggregate or query.

SELECT 
    SUM(CONVERT(DECIMAL(5, 2), (
        SELECT CONVERT(DECIMAL(5, 2), COUNT(*))
        FROM (
            SELECT PG_DATA
            FROM SPG010
            WHERE YEAR(PG_DATA) = '2017'
                AND MONTH(PG_DATA) = '01'
                AND PG_MAT = RA_MAT
            GROUP BY PG_DATA
        ) AS dias_trab
    ) / 26)) AS presenca
FROM SRA010 AS sra
WHERE 
    (
        RA_SITFOLH NOT IN ('D', 'A') OR 
        RA_DEMISSA > CAST(YEAR('20170116') AS VARCHAR) + CAST(REPLICATE('0', 2 - LEN(MONTH('20170116'))) + RTrim(MONTH('20170116')) AS VARCHAR) + '31'
    ) AND 
    SUBSTRING(RA_CC, 1, 5) = '13604'
sql sql-server query

  • What returns when you do only subselect?

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

2 answers

1

The problem is in using the subquery within a SUM. You can get around this problem by changing your subquery for a JOIN:

select sum(convert(decimal(5, 2), x.quantidade)) as presenca
  from sra010 as sra
       left join (select convert(decimal(5, 2), count(*)) / 26 as quantidade,
                         pg_mat
                    from (select pg_data,
                                 pg_mat
                            from spg010
                           where year(pg_data) = '2017'
                             and month(pg_data) = '01'
                           group by pg_data, pg_mat) as dias_trab) x on x.pg_mat = ra_mat
 where (ra_sitfolh not in ('D', 'A')
         or ra_demissa > cast(year('20170116') as varchar)
                         + cast(replicate('0', 2 - len(month('20170116'))) + rtrim(month('20170116')) as varchar)
                         + '31')
   and substring(ra_cc, 1, 5) = '13604'

Observing: Without the structure of the tables and what is expected of the result the only possible answer is generic. Your query can be improved, but for this we need a more specific vision.

0

How can I use sum with a subquery inside?

Documentation of the function Sum() indicates that the parameter must be an expression. And, in another part, it states that "(...) Aggregation functions and sub-discharge is not permitted". That is, your code must be rewritten.

Sum() is one of several aggregation functions available in T-SQL. Generally the aggregation functions are used in conjunction with the clause GROUP BY, but may be used in isolation in some specific cases.

For example, to add up the salaries paid in 2016 to each employee of the company, a way would be:

-- código #1
SELECT P.idFunc, Sum(P.Salário_Mensal) as Salário_Anual
  from Pagamento as P
  where P.AnoPagamento = 2016
  group by P.idFunc;

Note that the column list can only contain expressions of the GROUP BY clause and aggregation functions, as well as constants.

But if we add up all the salaries paid in 2016, we can have:

-- código #2
SELECT Sum(P.Salário_Mensal) as Total_salários
  from Pagamento as P
  where P.AnoPagamento = 2016;

In this case there is no GROUP BY clause as all lines were considered as a single group.

That being said, you should consider each sub-allowance as an individual query to apply the rules of use of the aggregation function.

Just as an example, to calculate the total wages paid in 2016 we can have

-- código #3
SELECT Sum(Salário_Anual) as Total_salários
  from (SELECT P.idFunc, Sum(P.Salário_Mensal) as Salário_Anual
          from Pagamento as P
          where P.AnoPagamento = 2016
        group by P.idFunc
       ) as T;

Note that both internal and external queries use the Sum() function, but in the case of the sub-consultation there is the GROUP BY clause, because the sum is performed for each grouping.

Complementing, for good readability of the code, as well as facilitating maintenance, I suggest you break the code in parts, using CTE. This is possible when there is no correlation between queries. For example:

-- código #4
with Salários_Func as (
SELECT P.idFunc, Sum(P.Salário_Mensal) as Salário_Anual
  from Pagamento as P
  where P.AnoPagamento = 2016
  group by P.idFunc
)
SELECT Sum(Salário_Anual) as Total_salários
  from Salários_Func;

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.