SQL Question

Postgresql : how can i get desired output as described below

I have a table named

Box
:

Box contains the following items :

1. pen

2. pencil

3. scale

4. eraser

5. sharpener


The actual table structure looks like this :

No Item quantity bought_date
_______________________________________________

1. pen 3 14-12-2016

2. pencil 10 15-12-2016

3. pen 5 16-12-2016

4. eraser 7 16-12-2016

5. sharpener 6 17-12-2016

6. pencil 3 18-12-2016

7. pen 3 18-12-2016


output needed:

Item Total_quantity
___________________________

Pencil 13

Pen 11

Eraser 7

Sharpener 6

Scale 0
___________________________

total 37


Please help me with this query...

Answer Source

You can do this:

SELECT t.item,sum(cast(coalese(b.quantity,'0') as integer)) as total_quantity FROM (
    SELECT 'pen' as item
    UNION
    SELECT 'pencil' as item
    UNION
    SELECT 'scale' as item
    UNION
    SELECT 'eraser' as item
    UNION
    SELECT 'sharpener' as item) t
LEFT OUTER JOIN box b
ON(t.item = b.item)
GROUP BY t.item
UNION
(SELECT 'total',sum(quantity)
FROM box)

I've adjusted the code according to your comments, I think this is what you want.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download