How to use Sum with subquery

Question

How to use Sum with subquery

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

How can I use sum with a subquery inside? I tried in many ways, but I could not.

When I try to put sum right after the main select , I get the message that I can not use aggregate 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

asked by anonymous 20.02.2017 / 12:38

2 answers

MongoDb Driver Fields Exclude How do I make a left join with criteria with a n n mapped table in java?

score 1 · Answer 1

The problem is to use subquery within SUM . You can work around this by changing your subquery to 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'

Note: 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 view.

score 0 · Answer 2

How can I use sum with a subquery inside?

The documentation for the function Sum () states that the parameter must be an expression. And, elsewhere, it cites that " (...) Aggregate functions and subqueries are not allowed ". That is, your code should be rewritten.

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

For example, to add the salaries paid in 2016 to each employee of the company, one 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 GROUP BY clause expressions and aggregate functions in addition to constants.

But if it is to 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, since all rows have been considered as a single group.

That said, you should consider each subquery as an individual query to apply the aggregate function usage rules.

Just as an example, to calculate the total salaries 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 queries, both internal and external, use the Sum () function, but in the case of the subquery there is the GROUP BY clause, since the sum is performed for each grouping.

Complementing, for good code readability as well as ease of maintenance, I suggest breaking the code into 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;