显示"Null"的聚合查询结果

Question

我在SQL Express 2016中遇到查询问题。我在单个表中提供了打印跟踪数据，其中显示了：

设备
1. 序列号(设备型号(
2. 部门成本编号(用户成本代码(
3. 用户名(用户登录(
4. 作业打印时间(开始日期时间(
页数
5. (跟踪页数(
6. 如果打印是彩色的(JobIsColor(
如果打印是双面打印
7. (作业是双面打印(
8. 作业的相关成本(价格(

现在，我正在尝试获取一个结果表，其中显示按设备序列号分组的打印总页数，然后是部门成本编号，最后是用户名。

理想情况下，输出如下所示：

Device Serial   Cost Code  User Name      ColSpx   ColDpx   MonSpx   MonDpx   Price
CH238713498     2665473    Angela Rippon   43       22        245     566      £ 20
CH238713498     2665473    Warwick Davis    2        0       1000     230      £ 30
CH238713498     2623379    Carl Carlson    53        0       2145    1566      £ 55
CH238713498     2664443    Warwick Davis    2        0       1000     230      £ 30

我一直在使用的查询(并且更改但没有成功(是：

DECLARE @ColourSimplex bigint SELECT TrackingPageCount
FROM TrackingTable    
WHERE JobIsColor = 1 AND JobIsDuplex = 0 AND JobType < 4 AND TrackingState = 2 AND (UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%SYSTEM%') AND TrackingPageCount > 0
DECLARE @ColourDuplex bigint SELECT TrackingPageCount
FROM TrackingTable
WHERE JobIsColor = 1 AND JobIsDuplex = 1 AND JobType < 4 AND TrackingState = 2 AND (UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%SYSTEM%') AND TrackingPageCount > 0
DECLARE @MonoSimplex bigint SELECT TrackingPageCount
FROM TrackingTable    
WHERE JobIsColor = 0 AND JobIsDuplex = 0 AND JobType < 4 AND TrackingState = 2 AND (UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%SYSTEM%') AND TrackingPageCount > 0
DECLARE @MonoDuplex bigint SELECT TrackingPageCount
FROM TrackingTable
WHERE JobIsColor = 0 AND JobIsDuplex = 1 AND JobType < 4 AND TrackingState = 2 AND (UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%SYSTEM%') AND TrackingPageCount > 0
SELECT  DeviceModel,
        UserCostCode,
        UserLogon,
        SUM(@ColourSimplex) AS 'Colour Simplex',
        SUM(@ColourDuplex) AS 'Colour Duplex',
        SUM(@MonoSimplex) AS 'Mono Simplex',
        SUM(@MonoDuplex) AS 'Mono Duplex',
        SUM(Price) AS 'Cost'
FROM TrackingTable
WHERE   StartDateTime BETWEEN '2017-05-31 23:59:00' AND '2017-08-31 23:59:00'
GROUP BY DeviceModel,
        UserCostCode,
        UserLogon
ORDER BY DeviceModel, UserCostCode, UserLogon

这是我得到的结果的示例：

L7L3Y01090  100951      MARINA SIRBU            NULL    NULL    NULL    NULL    0.84
L7L3Y01090  A01016-DE06 MADELEINE MCALLISTER    NULL    NULL    NULL    NULL    0.168
L7L3Y01090  A02026-DE06 DANIEL POULTER          NULL    NULL    NULL    NULL    0.448

关于如何解决此问题的任何帮助将不胜感激。

Answer 1

SELECT  DeviceModel,
        UserCostCode,
        UserLogon,
        SUM(CASE WHEN JobIsColor = 1 AND JobIsDuplex = 0 AND JobType < 4 AND TrackingState = 2 AND (UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%name%' AND UserLogon NOT LIKE '%SYSTEM%') AND TrackingPageCount > 0 THEN TrackingPageCount  ELSE 0 END) AS 'Colour Simplex',
        SUM(Price) AS 'Cost'
FROM TrackingTable
WHERE   StartDateTime BETWEEN '2017-05-31 23:59:00' AND '2017-08-31 23:59:00'
GROUP BY DeviceModel,
        UserCostCode,
        UserLogon
ORDER BY DeviceModel, UserCostCode, UserLogon

添加其他列...