SQL Query - SUM(CASE WHEN x THEN 1 ELSE 0) for multiple columns
I would change the query in the following ways:
- Do the aggregation in subqueries. This can take advantage of more information about the table for optimizing the
group by
. - 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. - By using
count(<fieldname>)
you can eliminate the comparisons tois null
. This is important for the second and third calculated values. - To combine the second and third queries, it needs to count an id from the
mde
table. These usemde.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
Preserve SQL Indexes While Altering Column Datatype
How to Retrieve Same Column Twice with Different Conditions in Same Table
SQL Server Unpivot Multiple Columns
Only Show Effective SQL String P6Spy
Do I Need to Call Rollback If I Never Commit
Ms Access Create Table with Autoincrement and Default Date
SQL Server Fuzzy Search with Percentage of Match
Postgres SQL 'Could Not Determine Data Type of Parameter' by Hibernate
How to Expand a "Condensed" Postgresql Row into Separate Columns
How to Use SQL Wildcards in Linq to Entity Framework
How to Replace Null Values with a Text
How to Calculate Running Multiplication
How to List the Source Table Name of Columns in a View (SQL Server 2005)
Conversion Failed When Converting from a Character String to Uniqueidentifier