Reputation: 532
Hi I have three tables here is all First. count_internets
id| company_id | item_id | created_at
1 | 1 | 1 | 2020-10-14 |
2 | 1 | 2 | 2020-10-15 |
3 | 2 | 4 | 2020-10-16 |
4 | 2 | 5 | 2020-11-20 |
5 | 1 | 1 | 2020-11-22 |
6 | 1 | 1 | 2020-11-23 |
7 | 2 | 5 | 2020-11-23 |
Second compaies
id | name
1 | Company 1
2 | Company 2
Third items
id | name | company_id
1 | Product 1 | 1
2 | Product 2 | 1
3 | Product 4 | 2
4 | Product 5 | 5
I want to get infomartion how many items sold during one month grouping by company like below
| Company | Products | Month | Count
| Company 1 | Product 1 | OCT | 1
| Company 1 | Product 2 | OCT | 1
| Company 1 | Product 1 | NOV | 1
| Company 1 | Product 4 | OCT | 1
| Company 2 | Product 5 | NOV | 2
I tried many SQL queries, but I can not solve. Please help to solve this query.
Upvotes: 0
Views: 35
Reputation: 876
This one should work
SELECT c.name as company, i.name as products, EXTRACT(MONTH FROM FROM_UNIXTIME(created_at)) as month, count(*)
FROM count_internets
JOIN companies as c on company_id = c.id
JOIN items as i on item_id = i.id
GROUP BY company, products, month
Be aware that if that the query extract only the month (so no difference between years). If you need the year too you have to extract it EXTRACT(YEAR FROM FROM_UNIXTIME(created_at)) as year
.
But your example is not very clear, company 1 didn't sell product 3. Furthermore, what's the meaning of the company_id in items table?
Upvotes: 1