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

If ItemName is unique, won't the Price for each ItemName be whatever is associated with that ItemName?

If you're looking for the sum of all Item prices, you would have to leave out the ItemName from your SQL:

SELECT SUM(Price) FROM Items

Answer 2

Have you tried

 SELECT SUM(Price) FROM Items

Answer 3

If ItemName is unique, then the aggregated result will contain the same number of rows as the table. The SUM function will group by a column where duplicates exist to give you a SUM for all columns with that value:

SELECT ItemName, SUM(Price)
FROM [Table]
GROUP BY ItemName

Table Values:

 ItemName   Price
    ========   =====
    Foo        $1.00
    Bar        $5.00
    Bar        $5.00

Aggregate Result:

ItemName   Price
========   =====
Foo        $1.00
Bar        $10.00

Answer 4

If you want the sum of all of them, why are you including ItemName in your query? You have two choices, include the ItemName and get the sum of all prices for that ItemName using "GROUP BY ItemName", or you can get the sum of all items, in which case you drop the "ItemName," from the select.

Answer 5

You can't both group and not group in the same query. You can use a subquery to get the price:

select ItemName, (select sum(Price) from Items) as AllPrices
from Items

As the subquery doesn't use any data from the records in the main query, the database should be able to optimise it to only execute the subquery once.

Answer 6

It wants to you do:

select ItemName, sum(price)
from table
group by ItemName;

If you want the sum of ALL the items, what is the meaning of having the ItemName reported?

table:

item   price
spoon  2
spoon  4
fork   3
plate  6

For the above table, the sum is 15. What is the item name of the sum?

Answer 7

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

Answer 8

Since ItemName is unique, you need to use the following...

SELECT SUM(Price) FROM Items

Answer 9

Perhaps what you're after is a list of items, their prices, as well as a Total of the prices?

SELECT ItemName, SUM(Price) Price
FROM Items
GROUP BY ROLLUP(ItemName);

ITEMNAME  PRICE
========  =====
Apples     1.00
Bananas    2.00
Cherries   3.00
           6.00