CODEWITHSUNDEEP
sql-server
skyline01
skyline01

Reputation: 2101

SQL Server: how to use 2+ aggregate functions on different columns in a SELECT statement?

I am working in SQL Server 2012. I have the following simple table:

CREATE TABLE t
(
    NK1 varchar(255)
    ,NK2 varchar(255)
    ,ID int
    ,amount decimal(10,2)
);

INSERT INTO t
    SELECT 'a', 'x', 3, 10.00 UNION ALL
    SELECT 'a', 'x', 1, 5.00 UNION ALL
    SELECT 'a', 'x', 0, 15.00 UNION ALL
    SELECT 'a', 'y', 1, 0.00 UNION ALL
    SELECT 'a', 'y', 10, -5.00 UNION ALL
    SELECT 'b', 'x', 2, 10.00 UNION ALL
    SELECT 'b', 'y', 0, 0.00 UNION ALL
    SELECT 'b', 'y', -1, 15.00 UNION ALL
    SELECT 'b', 'y', 3, 10.00 UNION ALL
    SELECT 'b', 'y', 15, 10.00;

Columns NK1 and NK2 define the natural key for the table. My goal is to return the max row number (for each natural key: ORDER BY NK1 ASC, NK2 ASC, ID ASC), the corresponding amount value, and the sum of the amount column for each natural key.

So, for the given table, I expect the following result set:

NK1 || NK2 || max_RowNumber || amount || sum_amount
a   | x    | 1              | 10.00   | 30.00
a   | y    | 2              | -5.00   | -5.00
b   | x    | 1              | 10.00   | 10.00
b   | y    | 4              | 10.00   | 35.00

I am having troubles using 2+ aggregate functions in a single SELECT statement (obviously, on 2+ different columns).

Here is my attempt:

WITH cte AS
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY NK1, NK2 ORDER BY NK1, NK2, ID) AS RowNumber
    FROM
        t
)
SELECT
    NK1, NK2,
    MAX(RowNumber) AS max_RowNumber,
    amount,
    SUM(amount) AS sum_amount
FROM
    cte
GROUP BY
    NK1, NK2;

When I run the above query, I get the following error:

Incorrect syntax near the keyword 'From'.

and it's referring to the "FROM cte" line. I believe this is occurring because of the 2+ aggregate functions on different columns in the SELECT.

In particular, the "SUM" line says that

'SUM' is not a recognized built-in function name.

How do I resolve this?

Upvotes: 0

Views: 374

Answers (4)

McNets
McNets

Reputation: 10827

I'd suggest next approach:

CTE calculates RANK() (or ROW_NUMBER) but I ordered by NK1, NK2, ID DESC It allows a INNER JOIN with t table WHERE RowNumber = 1, hence you can get ID and amount of max row number.

WITH ctMax AS
(
    SELECT NK1, 
           NK2,
           ID,
           RANK() OVER (PARTITION BY NK1, NK2 ORDER BY NK1, NK2, ID DESC) AS RK
    FROM   t
)
SELECT     t.NK1, t.NK2, t.ID, t.amount,
           (SELECT SUM(amount)
            FROM  t t2
            WHERE t2.NK1 = t.NK1
            AND   t2.NK2 = t.NK2) as TotalAmount
FROM       t
INNER JOIN ctMax cm
ON         t.NK1 = cm.NK1
AND        t.NK2 = cm.NK2
AND        t.ID  = cm.ID
WHERE      cm.RK = 1;


NK1 | NK2 | ID | amount | TotalAmount
:-- | :-- | -: | :----- | :----------
a   | x   |  3 | 10.00  | 30.00      
a   | y   | 10 | -5.00  | -5.00      
b   | x   |  2 | 10.00  | 10.00      
b   | y   | 15 | 10.00  | 35.00

dbfiddle here

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

I already upvoted the answer by Zohar because he cleared the error first, but I want to know why you are using row_number() like this:

with cte as (
 select *
  , row_number() over (
      partition by NK1, NK2 
      order by  NK1, NK2,ID /* NK1, NK2 don't do anything here either */
   ) as RowNumber
 from t
 )
select 
     NK1
   , NK2
   , max(RowNumber) as max_RowNumber
   , sum(amount) as sum_amount
from cte
group by NK1, NK2;

How is it not the same as this?

select 
     NK1
   , NK2
   , count(*) as max_RowNumber
   , sum(amount) as sum_amount
from t
group by NK1, NK2;

rextester demo showing the same results: http://rextester.com/XSGIP25353

Upvotes: 0

STLDev
STLDev

Reputation: 6174

The error is caused by the fact that your final SELECT statement contains amount, yet amount is not in your GROUP BY.

If you take amount out of your final query, (not the SUM(amount), it works fine.

WITH cte AS (
SELECT *
    ,ROW_NUMBER() OVER (
      PARTITION BY
        NK1
        ,NK2
      ORDER BY
        NK1
        ,NK2
        ,ID
      ) AS RowNumber
FROM
  t
)
SELECT
   NK1
   ,NK2
   ,MAX(RowNumber) AS max_RowNumber
   ,SUM(amount) AS sum_amount
FROM cte
GROUP BY
NK1
,NK2;

RESULTS:

NK1  NK2  max_RowNumber  sum_amount
---- ---- -------------- ----------
a    x    3              30.00
b    x    1              10.00
a    y    2              -5.00
b    y    4              35.00

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

I got a different error:

Column 'cte.amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When removing the ,amount from the query, it works fine:

;

WITH cte AS
(
SELECT
  *
  ,ROW_NUMBER() OVER (
                     PARTITION BY
                       NK1
                       ,NK2
                     ORDER BY
                       NK1
                       ,NK2
                       ,ID
                     ) AS RowNumber
  FROM
      t
)
SELECT
   NK1
   ,NK2
   ,MAX(RowNumber) AS max_RowNumber
   ,SUM(amount) AS sum_amount
FROM
   cte
GROUP BY
   NK1
   ,NK2
;

Results:

NK1     NK2     max_RowNumber   sum_amount
a       x       3               30,00
b       x       1               10,00
a       y       2               -5,00
b       y       4               35,00

See for yourself in rextester

Upvotes: 2

Related Questions