Hayrulla Melibayev
Hayrulla Melibayev

Reputation: 532

Count nested select using join and by month

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

Answers (1)

Hyruma92
Hyruma92

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

Related Questions