CODEWITHSUNDEEP
sql-serveraggregate-functionsouter-join
Isaac Reefman
Isaac Reefman

Reputation: 597

Grouping aggregated data in separate tables

Related to my earlier question here, I'm trying to COUNT records in a HISTORY table and an OPPORTUNITIES table, and group them by the year and quarter of their CREATE_DATEs.

I want one row in the results grid for each quarter, with one column for HISTORY records and one for OPPORTUNITIES records:

+---------+---------------+---------------+
| Quarter | History Items | Opportunities |
+---------+---------------+---------------+
| 2017 Q1 |            15 |            25 |
| 2017 Q2 |            12 |            16 |
| 2017 Q3 |            13 |            18 |
| 2017 Q4 |            16 |            20 |
| 2018 Q1 |            17 |            18 |
+---------+---------------+---------------+

I can get the results I want for either table, but as soon as I try to join them I end up with issues. I've tried various JOIN and UNION versions, but can't seem to get it right. I can see potential issues in the coding with everything I try, but can't work out how to code something that won't have them. This is the working single table SELECT query:

SELECT DATENAME(year, H.CREATE_DATE) + ', Qtr' + DATENAME(quarter, H.CREATE_DATE) AS Period,
       COUNT(H.ID) AS [History Items]
FROM   HISTORY AS H 
GROUP BY DATENAME(year, H.CREATE_DATE), DATENAME(quarter, H.CREATE_DATE)
ORDER BY Period;

And one of the versions I've tried to add the second table:

SELECT DATENAME(year, O.CREATE_DATE) + ', Qtr' + DATENAME(quarter, O.CREATE_DATE) AS Period,
       COUNT(O.ID) AS Opportunities,
       COUNT(H.ID) AS [History Items]
FROM   OPPORTUNITY AS O FULL JOIN
       HISTORY AS H ON DATENAME(year, O.CREATE_DATE) + DATENAME(quarter, O.CREATE_DATE) =
                       DATENAME(year, H.CREATE_DATE) + DATENAME(quarter, H.CREATE_DATE)
GROUP BY DATENAME(year, O.CREATE_DATE), DATENAME(quarter, O.CREATE_DATE)
ORDER BY Period;

For easy testing and further clarity, here is a sample database with what I've been working on in sql fiddle.

Upvotes: 0

Views: 119

Answers (1)

Squirrel
Squirrel

Reputation: 24763

One easy way is to use UNION ALL to join both of the result. This is probably clearer and easier. You may also use FULL OUTER JOIN as what you attempted, but with CTE or derivied table 

; WITH 
hist as
(
     -- your hist query 
    SELECT DATENAME(year, H.CREATE_DATE) 
           + ', Qtr' + DATENAME(quarter, H.CREATE_DATE) AS Period,
           COUNT(H.ID) AS [History Items]
    FROM   HISTORY AS H 
    GROUP BY DATENAME(year, H.CREATE_DATE), DATENAME(quarter, H.CREATE_DATE)
),
opp as
(
     -- the opportunity query
     SELECT DATENAME(year, O.CREATE_DATE) 
            + ', Qtr' + DATENAME(quarter, O.CREATE_DATE) AS Period,
            COUNT(O.ID) AS [Opportunities]
     FROM   OPPORTUNITY AS O
     GROUP BY DATENAME(year, O.CREATE_DATE), DATENAME(quarter, O.CREATE_DATE)
),
CTE as
(
     -- union both query together
     SELECT Period, [History Items], [Opportunities] = 0
     FROM   hist h
     UNION ALL
     SELECT Period, [History Items] = 0, Opportunities
     FROM   Oppr
)
-- final result
SELECT Period, 
       [History Items] = SUM([History Items]), 
       [Opportunities] = SUM([Opportunities])
FROM   CTE
GROUP BY Period
ORDER BY Period

-- FULL OUTER JOIN version. using the same hist & opp from previous query
SELECT Period = COALESCE(h.Period, o.Period),
       [History Items] = SUM([History Items]), 
       [Opportunities] = SUM([Opportunities])
FROM   hist h
       FULL OUTER JOIN opp o ON h.Period = o.Period
GROUP BY COALESCE(h.Period, o.Period)
ORDER BY Period

Upvotes: 1

Related Questions