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


Read more here: https://stackoverflow.com/questions/68473622/sql-sales-by-product-category-and-group-by-client-id

Content Attribution

This content was originally published by Pierre-Alexandre at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: