aggregate in SQL

Question

I have a table like this

and need to aggregate like below

DROP TABLE IF EXISTS #tmp2
SELECT item, Quantity, Seconds
SUM(Quantity) AS total_quantity,
--SUM(Seconds) AS total_seconds,
--SUM(Seconds)/3600 AS items_per_hour
INTO #tmp2
FROM #tmp1
GROUP BY item

error message:#tmp1.quantity is invalid in the select list because it is not contained in either an aggregate function or the groupby clause — rgirl, Jun 29 '20 at 08:00
The error is literally telling you to problem. `Quantity` is both *inside* and *outside* aggregate function. — Thom A, Jun 29 '20 at 08:01

score 0 · Answer 1 · answered Jun 29 '20 at 08:02

if you want the sun of a column you can directly invoke the same column in select

    SELECT item

    SUM(Quantity) AS total_quantity,
    --SUM(Seconds) AS total_seconds,
    --SUM(Seconds)/3600 AS items_per_hour
    INTO #tmp2
    FROM #tmp1
    GROUP BY item

score 0 · Answer 2 · answered Jun 29 '20 at 08:02

0

For aggregation you do not need Quantity and Seconds. Try the following

SELECT 
   item,
   SUM(Quantity) AS total_quantity,
   SUM(Seconds) AS total_seconds,
   SUM(Seconds)/3600 AS items_per_hour
FROM #tmp1
GROUP BY 
  item

answered Jun 29 '20 at 08:02

zealous

7,336
4
16
36

aggregate in SQL

2 Answers2