9

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' desc

This gives me result

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13           | 3
Islamabad | 10           | 4

I 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' desc

It gives me result

Region    | ClosedServices
Karachi   | 8
Lahore    | 7
Islamabad | 4

I need to merge both results, and show ClosedServices beside the DFC column.

  • There's an inconsistency - your second query produces a column called ClosedYesterday but the example data says ClosedServices. – Michael Green Apr 19 '14 at 13:06
  • What does "merge" mean? – philipxy Apr 9 '18 at 21:33
15

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.Region

Not 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 SELECT from OSCL, grouped by U_REGN, with each of your three counters as separate SUM(CASE ...) statements akin to what you do currently for DFC. This will be a single table scan, at most, depending you your indexes & schema.

  • 2
    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 Righarts Apr 20 '14 at 8:47
  • @Simon - fair point, but that wasn't the OP's given scenario. – Michael Green Apr 20 '14 at 11:22
  • 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 :) – TheSarfaraz Apr 20 '14 at 14:37
6

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' desc
  • 1
    Thank 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! – TheSarfaraz Apr 20 '14 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.