SQL Query - Sum(Case When X Then 1 Else 0) for Multiple Columns

SQL Query - SUM(CASE WHEN x THEN 1 ELSE 0) for multiple columns

I would change the query in the following ways:

  1. Do the aggregation in subqueries. This can take advantage of more information about the table for optimizing the group by.
  2. Combine the second and third subqueries. They are aggregating on the same column. This requires using a left outer join to ensure that all data is available.
  3. By using count(<fieldname>) you can eliminate the comparisons to is null. This is important for the second and third calculated values.
  4. To combine the second and third queries, it needs to count an id from the mde table. These use mde.mdeid.

The following version follows your example by using union all:

SELECT CAST(Detail.ReceiptDate AS DATE) AS "Date",
SUM(TOTALMAILED) as TotalMailed,
SUM(TOTALUNDELINOTICESRECEIVED) as TOTALUNDELINOTICESRECEIVED,
SUM(TRACEUNDELNOTICESRECEIVED) as TRACEUNDELNOTICESRECEIVED
FROM ((select SentDate AS "ReceiptDate", COUNT(*) as TotalMailed,
NULL as TOTALUNDELINOTICESRECEIVED, NULL as TRACEUNDELNOTICESRECEIVED
from MailDataExtract
where SentDate is not null
group by SentDate
) union all
(select MDE.ReturnMailDate AS ReceiptDate, 0,
COUNT(distinct mde.mdeid) as TOTALUNDELINOTICESRECEIVED,
SUM(case when sd.ReturnMailTypeId = 1 then 1 else 0 end) as TRACEUNDELNOTICESRECEIVED
from MailDataExtract MDE left outer join
DTSharedData.dbo.ScanData SD
ON SD.ScanDataID = MDE.ReturnScanDataID
group by MDE.ReturnMailDate;
)
) detail
GROUP BY CAST(Detail.ReceiptDate AS DATE)
ORDER BY 1;

The following does something similar using full outer join:

SELECT coalesce(sd.ReceiptDate, mde.ReceiptDate) AS "Date",
sd.TotalMailed, mde.TOTALUNDELINOTICESRECEIVED,
mde.TRACEUNDELNOTICESRECEIVED
FROM (select cast(SentDate as date) AS "ReceiptDate", COUNT(*) as TotalMailed
from MailDataExtract
where SentDate is not null
group by cast(SentDate as date)
) sd full outer join
(select cast(MDE.ReturnMailDate as date) AS ReceiptDate,
COUNT(distinct mde.mdeID) as TOTALUNDELINOTICESRECEIVED,
SUM(case when sd.ReturnMailTypeId = 1 then 1 else 0 end) as TRACEUNDELNOTICESRECEIVED
from MailDataExtract MDE left outer join
DTSharedData.dbo.ScanData SD
ON SD.ScanDataID = MDE.ReturnScanDataID
group by cast(MDE.ReturnMailDate as date)
) mde
on sd.ReceiptDate = mde.ReceiptDate
ORDER BY 1;

Case and Sum of Multiple columns

Is this what you want?

SELECT SUM(CASE WHEN [M] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Tu] = '1' THEN 1 ELSE 0 END +
CASE WHEN [W] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Th] = '1' THEN 1 ELSE 0 END +
CASE WHEN [F] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Sa] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Su] = '1' THEN 1 ELSE 0 END
)
FROM Customers;

Adding Multiple SUM(CASE)s to Same Column

Try this adding the corresponding expressions to the inner CASE to get all 12 (x, DMVLx) pairs:

WITH 
T (DMMONTH) AS (VALUES 1 UNION ALL SELECT DMMONTH + 1 FROM T WHERE DMMONTH < 12)
SELECT
DMYEAR, DMMONTH, DMPTYP, DMPROD
, Sum (CASE WHEN DMTYPE = 'COST' THEN CASE DMMONTH WHEN 1 THEN DMVL01 WHEN 2 THEN DMVL02 ... WHEN 12 THEN DMVL12 END ELSE 0 END) AS COST
, Sum (CASE WHEN DMTYPE = 'RTNCST' THEN CASE DMMONTH WHEN 1 THEN DMVL01 WHEN 2 THEN DMVL02 ... WHEN 12 THEN DMVL12 END ELSE 0 END) AS RTNCST
FROM DWM, T
WHERE DMPTYP = 'M'
GROUP BY DMYEAR, DMMONTH, DMPTYP, DMPROD
ORDER BY 1, 3

How to use sum(case) with three conditions

If you want all three values to return the same thing, you should use IN():

SUM(
CASE
WHEN over05 IN ('1', 'X', '2') THEN 1
ELSE 0 END
) AS OK_05

If you want each value to return something different, you should use multiple WHEN ... THEN :

SUM(
CASE
WHEN over05 = '1' THEN 1
WHEN over05 = 'X' THEN 2
WHEN over05 = '2' THEN 3
ELSE 0 END
) AS OK_05

SUM using CASE WHEN SELECT

Without going into techniques to get around being able to SELECT inside an aggregate function. Let's step back and rethink your query all together. You are trying to solve it by aggregating the Lookup table but the aggregation is really on the Employee table with a join from the lookup table.

It also appears that you are trying to do conditional aggregation to PIVOT your result. But in either event if you do the following you will get the result you specify if you do the following:

SELECT
l.loc
,COUNT(DISTINCT e.Id) as EmployeesAtStaffSite1
FROM
Lookup l
INNER JOIN Employees e
ON e.loc = l.loc
AND e.pos = l.pos
WHERE
l.lbl = 'Staff Site 1'
GROUP BY
l.loc

Also note that if you change INNER JOIN to LEFT JOIN you can get 0 count for any locations that are staff site 1 that there are no employees assigned.

Multiple case statement sum

Can you check the sum of the tests you performed in your query, such as with the following?

SELECT 
wonum,
targstartdate,
targcompdate,
schedstart,
schedfinish,
actstart,
actfinish,
halflife,
CASE
WHEN test0 + test1 + test2 + test3 >= 3 THEN 'RED'
WHEN test0 + test1 + test2 + test3 = 2 THEN 'ORANGE'
WHEN test0 + test1 + test2 + test3 = 1 THEN 'YELLOW'
WHEN test0 + test1 + test2 + test3 = 0 THEN 'GREEN'
END AS Flag
FROM
(
SELECT .... yourquery
) s

EDIT 1: forgot to add that in your query, you should evaluate the major case (test0, right?) as 4, not 1, i.e.,

CASE 
WHEN wo.actfinish < wo.targcompdate THEN 4
ELSE 0
END AS test0,

That way, anything that fails Test Zero is automatically going to sum up to Red-level totals.

EDIT 2: adding the CASE statement to a GROUP BY. If you only want to see the statement and a count, this query should do what you are looking for.

SELECT 
CASE
WHEN test0 + test1 + test2 + test3 >= 3 THEN 'RED'
WHEN test0 + test1 + test2 + test3 = 2 THEN 'ORANGE'
WHEN test0 + test1 + test2 + test3 = 1 THEN 'YELLOW'
WHEN test0 + test1 + test2 + test3 = 0 THEN 'GREEN'
END AS Rating,
COUNT(wonum) AS TotalRecords
FROM
(
SELECT .... yourquery
) s

GROUP BY
CASE
WHEN test0 + test1 + test2 + test3 >= 3 THEN 'RED'
WHEN test0 + test1 + test2 + test3 = 2 THEN 'ORANGE'
WHEN test0 + test1 + test2 + test3 = 1 THEN 'YELLOW'
WHEN test0 + test1 + test2 + test3 = 0 THEN 'GREEN'
END


Related Topics



Leave a reply



Submit