Merge two SELECT queries with different WHERE clauses
I have one table of services. I need to merge two SELECT queries. Both have different where clauses. For example
SELECT U_REGN as 'Region', COUNT(callID) as 'OpenServices', SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC' FROM OSCL WHERE ([status] = - 3) GROUP BY U_REGN ORDER BY 'OpenServices' descThis gives me result
Region | OpenServices | DFC Karaci | 14 | 4 Lahore | 13 | 3 Islamabad | 10 | 4I have another query
SELECT U_REGN as 'Region', COUNT(callID) as 'ClosedYesterday' FROM OSCL WHERE DATEDIFF(day, closeDate, GETDATE()) = 1 GROUP BY U_REGN ORDER BY 'ClosedYesterday' descIt gives me result
Region | ClosedServices Karachi | 8 Lahore | 7 Islamabad | 4I need to merge both results, and show ClosedServices beside the DFC column.
What does "merge" mean?
Treat your two current queries' resultsets as tables and join them:
select FirstSet.Region, FirstSet.OpenServices, FirstSet.DFC, SecondSet.ClosedYesterday from ( SELECT U_REGN as 'Region', COUNT(callID) as 'OpenServices', SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC' FROM OSCL WHERE ([status] = - 3) GROUP BY U_REGN --ORDER BY 'OpenServices' desc ) as FirstSet inner join ( SELECT U_REGN as 'Region', COUNT(callID) as 'ClosedYesterday' FROM OSCL WHERE DATEDIFF(day, closeDate, GETDATE()) = 1 GROUP BY U_REGN --ORDER BY 'ClosedYesterday' desc ) as SecondSet on FirstSet.Region = SecondSet.Region order by FirstSet.RegionNot the prettiest bit of SQL I've ever written but hopefully you'll see how it works and understand how to maintain it.
I suspect a better-performing query would be a single
SELECTfrom OSCL, grouped by U_REGN, with each of your three counters as separateSUM(CASE ...)statements akin to what you do currently for DFC. This will be a single table scan, at most, depending you your indexes & schema.What happens when there's results in one subquery that aren't there in the other? I'd suspect you actually want a full outer join here.
@Simon - fair point, but that wasn't the OP's given scenario.
Thank You, this is what I wanted, Thanks! And also Thanks @SimonRigharts . Inner join wasnt showing me all results I intended, so I Used full outer join, works perfectly :)
Building off Michael's suggestion:
SELECT U_REGN AS 'Region', SUM(CASE WHEN [status] = -3 THEN 1 ELSE 0 END) AS 'OpenServices', SUM(CASE WHEN [status] = -3 AND [description] LIKE '%DFC%' THEN 1 ELSE 0 END) AS 'DFC', SUM(CASE WHEN DATEDIFF(day, closeDate, GETDATE()) = 1 THEN 1 ELSE 0 END) AS 'ClosedYesterday' FROM OSCL GROUP BY U_REGN ORDER BY 'OpenServices' descThank You Simon, but I used @Michael Green's query with full outer join because this query gives me even those regions which doesnt have any open or any closed service!
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Michael Green 6 years ago
There's an inconsistency - your second query produces a column called ClosedYesterday but the example data says ClosedServices.