ISNULL and UPPER with GROUP BY

Question

我有一个查询(精简版 - 为清楚起见，编辑以添加 SUM 项目( -

SELECT STORENBR AS "Store", 
DISPENSER AS "Model",
UPPER(TINTERSERIALNBR) AS "Serial",
SUM (CASE WHEN CLRNTSYS ='CCE' THEN GALLONS ELSE 0 END) AS "2018 CCE gallons",
SUM (CASE WHEN CLRNTSYS ='BAC' THEN GALLONS ELSE 0 END) AS "2018 BAC gallons",
SUM (CASE WHEN CLRNTSYS ='844' THEN GALLONS ELSE 0 END) AS "2018 844 gallons" 
FROM table
GROUP BY STORENBR, DISPENSER, UPPER(TINTERSERIALNBR);

工作得很好。 但是现在我必须考虑序列号字段中的 NULL 值。 我试过了

SELECT STORENBR AS "Store", 
DISPENSER AS "Model",
ISNULL(UPPER(TINTERSERIALNBR), "N/A") AS "Serial",
SUM (CASE WHEN CLRNTSYS ='CCE' THEN GALLONS ELSE 0 END) AS "2018 CCE gallons",
SUM (CASE WHEN CLRNTSYS ='BAC' THEN GALLONS ELSE 0 END) AS "2018 BAC gallons",
SUM (CASE WHEN CLRNTSYS ='844' THEN GALLONS ELSE 0 END) AS "2018 844 gallons"  
FROM table
GROUP BY STORENBR, DISPENSER, UPPER(TINTERSERIALNBR);

Oracle SQL Developer告诉我ISNULL不能与GROUP BY一起使用。

我该如何完成这项工作？

Answer 1

您可以通过添加整个表达式在单个查询中做到这一点：

SELECT STORENBR AS "Store", 
DISPENSER AS "Model",
NVL(UPPER(TINTERSERIALNBR), "N/A") AS "Serial",
SUM (CASE WHEN CLRNTSYS ='CCE' THEN GALLONS ELSE 0 END) AS "2018 CCE gallons",
SUM (CASE WHEN CLRNTSYS ='BAC' THEN GALLONS ELSE 0 END) AS "2018 BAC gallons",
SUM (CASE WHEN CLRNTSYS ='844' THEN GALLONS ELSE 0 END) AS "2018 844 gallons"  
FROM table
GROUP BY STORENBR, DISPENSER, NVL(UPPER(TINTERSERIALNBR), "N/A");

Answer 2

不是ISNULL而是NVL，两次 - 在select和group by语句中

SELECT STORENBR AS "Store", 
DISPENSER AS "Model",
NVL(UPPER(TINTERSERIALNBR), "N/A") AS "Serial",
SUM (CASE WHEN CLRNTSYS ='CCE' THEN GALLONS ELSE 0 END) AS "2018 CCE gallons",
SUM (CASE WHEN CLRNTSYS ='BAC' THEN GALLONS ELSE 0 END) AS "2018 BAC gallons",
SUM (CASE WHEN CLRNTSYS ='844' THEN GALLONS ELSE 0 END) AS "2018 844 gallons"  
FROM table
GROUP BY STORENBR, DISPENSER, NVL(UPPER(TINTERSERIALNBR), "N/A");

Answer 3

将查询包装到派生表中，然后执行 分组依据：

select "Store", 
"Model",
"Serial",
SUM (CASE WHEN CLRNTSYS ='CCE' THEN GALLONS ELSE 0 END) AS "2018 CCE gallons",
SUM (CASE WHEN CLRNTSYS ='BAC' THEN GALLONS ELSE 0 END) AS "2018 BAC gallons",
SUM (CASE WHEN CLRNTSYS ='844' THEN GALLONS ELSE 0 END) AS "2018 844 gallons" 
from
(
SELECT STORENBR AS "Store",
DISPENSER AS "Model",
ISNULL(UPPER(TINTERSERIALNBR), "N/A") AS "Serial",
CLRNTSYS,
GALLONS 
FROM table
) dt
GROUP BY "Store", "Model", "Serial"