首页 > 解决方案 > SQL - 按产品类别和按客户 ID 分组的销售额

问题描述

我有 2 个表,一个销售表和一个产品表。我正在尝试查询这些表以获取每个 client_id 的产品类别的总销售额。

异常结果:

| 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

销售.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       |

产品.sql

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

我正在尝试但很难完成的事情:

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

标签: sql

解决方案


你想要一个join和聚合:

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 ;

推荐阅读