How can I join the SUMS from 2 different tables into 1

Question

I have 2 tables

Table 1 = LOG

Site   Year   Quarter    SF    Seats  
 ------ ------ --------- ------ ------- 
  NYC    2019   Q1        1000      34  
  NYC    2019   Q1        1289      98  
  CHI    2019   Q1         976      17  
  NYC    2019   Q2        3985     986 

Table 2 = Headcount

Site   Year   Quarter   HC     
 ------ ------ --------- -------  
  NYC    2019   Q1           63     
  NYC    2019   Q1           34     
  CHI    2019   Q1           73     
  NYC    2019   Q2           23  

I need to be able to join these tables together and display the sum of SF, Seats, and HC for each distinct Site, Quarter, and Year

For example the output should be:

 Site   Year   Quarter   HC    SF    Seats  
 ------ ------ --------- ------- ------ ------- 
  NYC    2019   Q1           97   2289     132  
  NYC    2019   Q2           23   3985     986  
  CHI    2019   Q1           73    976      17 

Here is my SQL Query:

SELECT DISTINCT SITE,
                YEAR,
                QUARTER,
                SEATS,
                SF,
                HC
FROM
  (SELECT DISTINCT site SITE,
                   YEAR YEAR,
                        quarter QUARTER,
                        sum(SEATS) SEATS,
                        sum(SF) SF
   FROM Headcount
   GROUP BY SITE,
            YEAR,
            QUARTER) A
CROSS JOIN
  (SELECT DISTINCT sum(HC) HC
   FROM Headcount
   GROUP BY site,
            YEAR,
            quarter, HC) C

But I am getting this error message "Column HC contains an aggregation function, which is not allowed in GROUP BY"

Any idea what I'm doing wrong and why this query isnt working?

Answer 1

The reason for the error is that in the last sub query you have HC in the group by clause, while you also aggregate with sum(HC). That is not allowed. It should be one or the other.

However, a cross join will combine all rows from the first sub query, with all rows from the second. Surely this is not what you need.

Also, distinct is not needed when you use group by. You cannot get duplicates with group by.

I would suggest using union all:

SELECT    SITE,
          YEAR,
          QUARTER,
          SUM(HC),
          SUM(SEATS),
          SUM(SF)
FROM      (
            SELECT  SITE,
                    YEAR,
                    QUARTER,
                    HC,
                    null AS SEATS,
                    null AS SF
            FROM    Headcount
            UNION ALL
            SELECT  SITE,
                    YEAR,
                    QUARTER,
                    null,
                    SEATS,
                    SF
            FROM    Log
          ) AS base
GROUP BY  SITE,
          YEAR,
          QUARTER

Answer 2

With a N-M relationships between both tables, you would need to do the aggregation in subqueries, and then join the results together :

SELECT h.*, l.SF, l.Seats
FROM 
    (
        SELECT site, year, quarter, SUM(SF) SF, SUM(Seats) Seats
        FROM LOG 
        GROUP BY site, year, quarter
    ) l
    INNER JOIN (
        SELECT site, year, quarter, SUM(HC) HC
        FROM Headcount 
        GROUP BY site, year, quarter
    ) h 
    ON h.site = l.site AND h.year = l.year AND h.quarter = l.quarter