Select of 2 related tables in MySQL

Question

I have these 2 tables:

Table Users:

id | name
---------
1  | Joe
2  | Sara
3  | Michael

Table Sales:

id |  product  | user_id
------------------------
1  |    Car    | 2
2  |   Truck   | 3
3  | motorcycle| 1
4  |    Car    | 2
5  |   Truck   | 1
6  |    Car    | 3
7  |    Car    | 2
8  |   Truck   | 3
9  |    Car    | 2
10 |    Car    | 3

I want the following:

User Name  | Car | Truck | Motorcycle
Joe        |  0  |   1   |     1
Sara       |  4  |   0   |     0
Michael    |  2  |   2   |     0

Any help would be appreciated

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Juan Carlos Oropeza, Nov 14 '17 at 15:37
Consider handling issues of data display in application code. — Strawberry, Nov 14 '17 at 15:38

score 0 · Answer 1 · answered Nov 14 '17 at 15:41

SELECT Users.name,
SUM(Case 
   WHEN product='Car' then 1
   ELSE 0
) AS Car,
SUM(Case 
   WHEN product='Truck' then 1
   ELSE 0
) AS Truck   ,
SUM(Case 
   WHEN product='Motorcycle' then 1
   ELSE 0
) AS Motorcycle,
FROM Users
LEFT JOIN Sales ON Users.id = Sales.user_id
GROUP BY Users.name

score 0 · Answer 2 · answered Nov 14 '17 at 15:43

SELECT
    name as UserName ,  
    SUM(product= 'Car')AS Car,
    SUM(product= 'Truck')AS Truck,
    SUM(product= 'motorcycle') AS motorcycle
FROM Users join Sales
on Users.id = Sales.user_id
GROUP BY name

hope it helps but still this is a standard solution you should check pivot Tables.

Rob Presto · Accepted Answer · 2017-11-14T16:25:14.960

0

--For a small number of products this works,

Select username
, Sum(case when product = 'Car' then 1 else 0 End) Cars
, Sum(case when product = 'Truck' then 1 else 0 End) Truck
, Sum(case when product = 'motorcycle' then 1 else 0 End) motorcycle
From Sales s join users u on s.userid = u.userid
group by username

edited Nov 14 '17 at 16:25

answered Nov 14 '17 at 15:45

Rob Presto

16
3

You can remove the second query MySQL doesnt support the pivot function.. – Raymond Nijland Nov 14 '17 at 16:02

Select of 2 related tables in MySQL

3 Answers3