Mike M Mike M - 26 days ago 9
SQL Question

Joining together 2 SQL queries to obtain weekday average and weekend average results in same query

I have two queries that work fine separately: one query extracts average sales of pens & pencils from a sales table for each salesperson for WEEKDAYS for each month of a year and the other query extracts average sales of pencils and pens from the same sales table for each salesperson for WEEKENDS for each month of a year. I can't figure out how to join the two queries together so that average sales for pencils and pens for weekends AND weekdays appear in the same result set.
My two separate working queries are:

++++++++++++++FIRST QUERY+++++++++++++++++

SELECT salesperson,
Avg([pencil_sales]) [pencil_salesAV],
Avg([pen_sales]) [pen_salesAV],
Month(the_date) Month,
Year(the_date) Year
FROM regionalsales
WHERE DATEPART(w,[the_date]) NOT IN (1,7)
GROUP BY GROUPING SETS((Month(the_date), Year(the_date), salesperson), (salesperson));


+++++++++++++++++SECONDQUERY++++++++++++++++

SELECT salesperson,
Avg([pencil_sales]) [pencil_salesAV],
Avg([pen_sales]) [pen_salesAV],
Month(the_date) Month,
Year(the_date) Year
FROM regionalsales
WHERE DATEPART(w,[the_date]) NOT IN (2,3,4,5,6)
GROUP BY GROUPING SETS((Month(the_date), Year(the_date), salesperson), (salesperson));


++++++++++++++++++++
I would appreciate advice on how to join these two queries into one result set. Thanks.

Answer

Use conditional aggregation:

SELECT salesperson, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) NOT IN (1, 7) THEN  [pencil_sales] END)) as pencil_salesAV_weekday, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) NOT IN (1, 7) THEN [pen_sales] END) as pen_salesAV_weekday, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) IN (1, 7) THEN  [pencil_sales] END)) as pencil_salesAV_weekend, 
       Avg(CASE WHEN DATEPART(weekday, [the_date]) IN (1, 7) THEN [pen_sales] END) as pen_salesAV_weekend, 
       Month(the_date) as Month,
       Year(the_date) as Year
FROM regionalsales 
GROUP BY GROUPING SETS((Month(the_date), Year(the_date), salesperson), (salesperson));