[FIXED] MySQL query that combines two tables and two columns in one of the tables

Issue

I'm trying to write an SQL query that combines two tables and two columns in one of the tables. So, I have two tables

Table: Items

ID          Material           Shape

1           glass              jar
2           plastic            bottle
3           cardboard          box
4           glass              bottle

Table: Diary

ItemID      UserID      Quantity

2           1           1
1           1           3
3           1           2
2           1           5
4           1           1

Expected output where UserID = 1 (sorted by combined quantity):

Combined column values       Combined quantity

plastic bottle               6
glass jar                    3
cardboard box                2
glass bottle                 1

Could someone direct me the right way?

Solution

I think this is just a join and group by:

select concat_ws(' ', i.material, i.shape) as combined,
       sum(d.quantity) as combined_quantity
from items i left join
     diary d
     on d.itemId = i.id
group by combined
order by combined_quantity desc;

Answered By - Gordon Linoff