CODEWITHSUNDEEP
mysqlsql
DikoB
DikoB

Reputation: 27

MySQL query - joining 3 tables count and group by one column

Here is the example of the 3 tables I'm working on

Orders

+----+----------+
| id | event_id |
+----+----------+
|  1 | 40       |
|  2 | 50       |
|  3 | 60       |
|  4 | 60       |
+----+----------+

Events

+----+------------+
| id | title      |
+----+------------+
|  40| Miserables |
|  50| Wicked     |
|  60| Christmas  |
+----+------------+

Order Items

+----+------------+----------+----------+
| id | order_id   | category | quantity |
+----+------------+----------+----------+
| 1  | 1          | VIP      |  2       |
| 2  | 1          | Gen Adm  |  5       |
| 3  | 2          | VIP      |  1       |
| 4  | 3          | Adult    |  1       |
| 5  | 3          | VIP      |  1       |
| 6  | 4          | VIP      |  3       |
| 7  | 4          | 4kids    |  2       |
+----+----------------------------------+

Desired Results

+----------+------------+----------+
| event_id | category   | count    |
+----------+------------+----------+
| 40       | VIP        |   2      |
| 40       | Gen Adm    |   5      |
| 50       | VIP        |   1      |
| 60       | Adult      |   1      |
| 60       | VIP        |   4      |
| 60       | 4kids      |   2      |
+----+-----------------------------+

I have tried this but not getting the desired result. Thanks again!

select orders.id, events.title, a.title from events LEFT JOIN orders ON orders.event_id = events.id LEFT JOIN (SELECT order_id, category, SUM(quantity) as qty from order_items GROUP BY category) as a on a.order_id = orders.id where orders.id is not null GROUP by a.category ORDER by events.title

Upvotes: 0

Views: 729

Answers (2)

ysth
ysth

Reputation: 98388

This is very straightforward:

select event_id,category,sum(quantity) as count
from order_items
join orders on orders.id=order_items.order_id
group by event_id,category;

Upvotes: 0

Geoduck
Geoduck

Reputation: 8995

Your subquery makes no sense to me. The entire query is as simple as:

SELECT events.id, order_items.category, SUM(order_items.quantity) AS count FROM events left join orders on events.id=orders.event_id LEFT JOIN order_items ON order_items.order_id = orders.id GROUP BY events.id, order_items.category

I think the key here is that you want to group by two columns. Grouping by event.id should be the same as event.title as long as title is unique. But you also need to group by category to get the proper total. And since you want the count, you actually need to include that in the columns you select.

But, order.id is not the same as event.id, event.title is not the same as order_items.category, and a.title is not the count.

Upvotes: 0

Related Questions