Show sum of all for every record

Question

Say you have table of some items with these two columns:

• ItemName
• Price

....where ItemName is unique.

How do you show sum of all prices for every ItemName (I'm using Oracle)? If I just try this:

SELECT ItemName, 
       SUM(Price)
  FROM Items

I get this error:

ORA-00937: not a single-group group function

...which goes away only if I use GROUP BY. But then I can SUM only by groups, not all of them.

Answer 1

SELECT  ItemName, SUM(Price) OVER()
FROM    Items

This query will return you the list of ItemName's along with the total sum of prices against each ItemName:

SELECT  ItemName, SUM(Price) OVER()
FROM    (
        SELECT  'Apple' AS ItemName, 100 AS price
        FROM    dual
        UNION ALL
        SELECT  'Banana', 200 AS price
        FROM    dual
        UNION ALL
        SELECT  'Cocoa', 300 AS price
        FROM    dual
        ) q


ItemName  Price
--------  -----
Apple     600
Banana    600
Cocoa     600