How to combine SELECT DISTINCT and SUM()

Question

I have a table like given bellow, in Oracle:

[Products]
Product_ID | Product_NME | Product_SUP | Quantity
=================================================
    1         Apple         USA           100
    2         Fish          Japan          50
    3         Wine          Italy          10
    4         Apple         China          30
    5         Fish          Germany        10

I need a query that will find the full Quantity for every Product_NME by DISTINCT.

The expected result should be:

apple 130
fish 60
wine 10

I've tried to modify it like the one shown here as:

SELECT
    distinct(Product_NME, Product_SUP), sum(Quantity)
FROM
    Products

But it's not my case. Also I've tried this one:

SELECT DISTINCT  Product_NME
FROM Products 
UNION
SELECT SUM(Quantity) FROM Products

But is also not working.

Can anyone help me with this?

Thanks

What is the expected result? E.g. do you want "Apple 100" or "Apple 30" or "Apple 130"? — Dmitry Bychenko, Sep 10 '14 at 06:23

score 12 · Accepted Answer · answered Sep 10 '14 at 06:28

12

DISTINCT is not the clause you are looking for!

GROUP BY is.

The following query will return with all products and the total quantity for each one.

SELECT
  Product_NME
  , SUM(Quantity) AS TotalQuantity
FROM
  Products 
GROUP BY
  Product_NME

answered Sep 10 '14 at 06:28

Pred

8,789
3
26
46

score 0 · Answer 2 · answered Sep 10 '14 at 06:20

0

SELECT Product_NME, SUM(Quantity) FROM Products GROUP BY Product_NME

answered Sep 10 '14 at 06:20

Felix Pamittan

31,544
7
41
67

3

Don't just post code an answer, add a brief explanation for a better answer. – Rich Sep 10 '14 at 06:39

score 0 · Answer 3 · answered Sep 10 '14 at 07:44

0

You don't need for distinct just group by product name :

SELECT  Product_NME , SUM(Quantity) AS TotalQTY 

FROM  Products 

GROUP BY Product_NME

answered Sep 10 '14 at 07:44

G.Nader

847
7
9

How to combine SELECT DISTINCT and SUM()

3 Answers3

Linked