phpmyadmin Question posted in
The official documentation can be found here.

MySQL query that combines two tables and two columns in one of the tables – Phpmyadmin

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    Query

    select Material+SPACE(1)+ SHAPE [Combined column values ], 
SUM(QUANTITY) [Combined quantity]
from Items 
left join diary on Items.itemiD = diary.itemID  
GROUP BY ITEMS.itemiD, Material, SHAPE

    Output

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search