PostgreSQL sum quantity of children items

Question

I have a subscription service that delivers many items.
Subscribers add items to a delivery by creating a row in delivery_items.
Until recently subscribers could only add 1 of each item to a delivery. But now I have added a quantity column to my delivery_items table.

Given this schema, and an outdated query (on SQL Fiddle), how can I select the total amount of an item I will need for each day's deliveries?

This provided a table of days, and items being delivered that day but doesn't account for quantity:

SELECT
  d.date, 
  sum((di.item_id = 1)::int) as "Bread",
  sum((di.item_id = 2)::int) as "Eggs",
  sum((di.item_id = 3)::int) as "Coffee"
FROM deliveries d
JOIN users u ON u.id = d.user_id
JOIN delivery_items di ON di.delivery_id = d.id
GROUP BY d.date
ORDER BY d.date

Ideally, my query would be agnostic to the specifics of the items, like the id/name.

Thanks

Edit to 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)

Answer 1

You don't need to JOIN your users table, because you're neither getting any data from it nor using it as your joining condition.

Here's your edited SQL Fiddle

Using conditional sum() function would retrieve values of needed goods to deliver for a particular date.

SELECT
  d.date, 
  sum(CASE WHEN di.item_id = 1 THEN di.quantity ELSE 0 END) as "Bread",
  sum(CASE WHEN di.item_id = 2 THEN di.quantity ELSE 0 END) as "Eggs",
  sum(CASE WHEN di.item_id = 3 THEN di.quantity ELSE 0 END) as "Coffee"
FROM deliveries d
JOIN delivery_items di ON di.delivery_id = d.id
GROUP BY d.date
ORDER BY d.date

You could also look into crosstab(text, text) function. Result would be the same, but you can also specify query that produces the set of categories.

Though, if you want to get dynamic results when your items table has additional rows, you would need to wrap this up in a function and build the output columns and types definition, because:

The crosstab function is declared to return setof record, so the actual names and types of the output columns must be defined in the FROM clause of the calling SELECT statemen