sql - PostgreSQL sum quantity of children items -

i have subscription service delivers many items.
subscribers add items delivery creating row in delivery_items.
until subscribers add 1 of each item delivery. have added quantity column delivery_items table.

given this schema, , outdated query (on sql fiddle), how can select total amount of item need each day's deliveries?

this provided table of days, , items being delivered day doesn't account quantity:

select   d.date,    sum((di.item_id = 1)::int) "bread",   sum((di.item_id = 2)::int) "eggs",   sum((di.item_id = 3)::int) "coffee" deliveries d join users u on u.id = d.user_id join delivery_items di on di.delivery_id = d.id group d.date order d.date 

ideally, query agnostic specifics of items, id/name.

thanks

edit add schema:

deliveries (table)  id int4(10)  date timestamp(29)  user_id int4(10) delivery_items (table)  delivery_id int4(10)  item_id int4(10)  quantity int4(10) items (table)  id int4(10)  name varchar(10) users (table)  id int4(10)  name varchar(10) 

you don't need join users table, because you're neither getting data nor using joining condition.

here's edited sql fiddle

using conditional sum() function retrieve values of needed goods deliver particular date.

select   d.date,    sum(case when di.item_id = 1 di.quantity else 0 end) "bread",   sum(case when di.item_id = 2 di.quantity else 0 end) "eggs",   sum(case when di.item_id = 3 di.quantity else 0 end) "coffee" deliveries d join delivery_items di on di.delivery_id = d.id group d.date order d.date 

you crosstab(text, text) function. result same, can specify query produces set of categories.

though, if want dynamic results when items table has additional rows, need wrap in function , build output columns , types definition, because:

the crosstab function declared return setof record, actual names , types of output columns must defined in clause of calling select statemen