只有当状态符合标准时才选择客户id

Question

我正试着从数据库中提取一些记录。在大多数情况下，我需要高水平的细节。但是，如果状态为3，则需要提取客户级别的信息。

它看起来像这样。然而，到目前为止，案例陈述似乎不起作用。

SELECT
    product as product,
    state as state,
    CASE WHEN state = 3 THEN customers ELSE 0 END as customerid,
    SUM(sales)
FROM database
GROUP BY
    product,
    state,
    CASE WHEN state = 3 THEN customers ELSE 0 END

我可以删除case语句，并提取每个州的客户编号。然而，回报太大了。我认为这将是理想的，只要得到客户号码，它是必需的，而在情况下，它是不需要的，我只是得到一个0。从而减小了输出的大小。

欢迎对最佳实践的任何帮助。

Oracle sql

Answer 1

我只想把这两个总数结合起来：

SELECT product as product,
       state as state,
       customers AS customerid,
       SUM(sales)
  FROM DATABASE
 WHERE state = 3
 GROUP BY product, state, customers
 UNION
SELECT product as product,
       state as state,
       0 AS customerid,
       SUM(sales)
  FROM DATABASE
 WHERE NVL(state,0) <> 3
 GROUP BY product, state;