CODEWITHSUNDEEP
sqloracle-databaseora-00937
Daar
Daar

Reputation: 3565

Show sum of all for every record

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

....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.

Upvotes: 2

Views: 14139

Answers (9)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

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

Upvotes: 0

pdavis
pdavis

Reputation: 3211

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

SELECT SUM(Price) FROM Items

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425723

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

Upvotes: 7

David Oneill
David Oneill

Reputation: 13105

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?

Upvotes: 0

Guffa
Guffa

Reputation: 700690

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.

Upvotes: 3

Paul Tomblin
Paul Tomblin

Reputation: 182822

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.

Upvotes: 2

Dave Swersky
Dave Swersky

Reputation: 34810

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

Upvotes: 0

James Keesey
James Keesey

Reputation: 1217

Have you tried 

 SELECT SUM(Price) FROM Items

Upvotes: 0

Kaleb Brasee
Kaleb Brasee

Reputation: 51955

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

Upvotes: 2

Related Questions