CODEWITHSUNDEEP
sql
Pierre-Alexandre
Pierre-Alexandre

Reputation: 775

SQL - Sales by product category and Group by client ID

I have 2 tables, a sales tables and a products table. I am trying to query those tables to get the total sales by product category for each client_id.

excepted result:

| client_id    | hardware sales | software sales |
|  C109        | 300            | 0              | 150 * 2 and 0 * 0
|  C145        | 160            | 240            | 160 * 1 and 10 * 24
|  C160        | 500            | 0              | 500 * 1 and 0 * 0

sales.sql

| date      | order_id | client_id | product_id| product_price | quantity |
| 2020-01-02| A456     | C109      | 2546      | 150           | 2        |
| 2020-01-04| A457     | C145      | 2546      | 160           | 1        |
| 2020-01-04| A458     | C160      | 3000      | 500           | 1        |
| 2020-01-06| A459     | C145      | 8712      | 10            | 24       |

products.sql

| product_id| product_type   | product_name   |
|  2546     | hardware       | keyboard       |
|  3000     | hardware       | screen         |
|  4445     | software       | video games    |
|  8712     | software       | malwarebytes   |

what I am trying but have hard too time to finish:

SELECT client_id FROM `sales` 
WHERE EXISTS (SELECT product_id      
FROM `products` 
WHERE 'product_id' = '4445')
GROUP BY client_id

Upvotes: 1

Views: 503

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

You want a join and aggregation:

select s.client_id,
       sum(case when product_type = 'hardware' then s.product_price * s.quantity else 0 end) hardware_sales,
       sum(case when product_type = 'software' then s.product_price * s.quantity else 0 end) software_sales
from sales s join
     products p
     on s.product_id = p.product_id
group by s.client_id ;

Upvotes: 1

Related Questions