Same and Multi Day Averages

Transact-SQL
,
1

I am trying to figure out if this is even possible, but I was wanting to write a query that will give me multiple averages and values, I am trying to pull together a persons sales count for the day, the average of everyone for the day, the same persons average for the week, and the average for everyone for the week.

Given the data below if I were running this for Bob on 7/14 I would want to see that he has 7 for the day, avg 5 per day for the week (select salesperson, avg(salecount) from data group by salesperson), the average for the day is 7 (select avg(salecount) from data where salesdate = '2017-07-14') and the average for everyone for the week is 5 (select avg(salecount) from data).

Is there a way to do this in a single query so I don't have to touch the data 4 times? I suppose I could use a temp table to alleviate it, but figured there has to be a better way.

create table data (
SalesDate date
,SalesPerson varchar(20)
,SaleCount int
);

insert into data values
('2017-07-10', 'Bob', 5),
('2017-07-10', 'John', 3),
('2017-07-10', 'Stan', 8),

('2017-07-11', 'Bob', 6),
('2017-07-11', 'John', 7),
('2017-07-11', 'Stan', 6),

('2017-07-12', 'Bob', 3),
('2017-07-12', 'John', 4),
('2017-07-12', 'Stan', 4),

('2017-07-13', 'Bob', 6),
('2017-07-13', 'John', 4),
('2017-07-13', 'Stan', 3),

('2017-07-14', 'Bob', 7),
('2017-07-14', 'John', 6),
('2017-07-14', 'Stan', 9)

2

Yes, GROUPING SETS can do this in one query. Here's a sample to give you an idea of how to do this; naturally you can tweak the final output to be whatever you want.

SELECT 
    CASE WHEN SalesPerson IS NULL THEN CASE WHEN SalesDay IS NULL THEN 'Everyone' ELSE 'Daily Avg' END ELSE '' END +
    ISNULL(SalesPerson, '') AS SalesPerson,
    ISNULL(CONVERT(varchar(10), SalesDay, 120), 'Weekly Avg') AS SalesDay,
    SaleCount / SalesDateCount AS Sales
FROM (
    SELECT SalesPerson, DATEADD(DAY, DATEDIFF(DAY, 0, SalesDate), 0) AS SalesDay, SUM(SaleCount) AS SaleCount, COUNT(*) AS SalesDateCount
    FROM dbo.data
    WHERE SalesDate >= DATEADD(DAY, -DATEDIFF(DAY, 0, @date) % 7, CAST(@date AS date)) AND
        SalesDate < DATEADD(DAY, -DATEDIFF(DAY, 0, @date) % 7 + 7, CAST(@date AS date))
    GROUP BY GROUPING SETS ( (SalesPerson), (SalesPerson, SalesDate), (SalesDate), () )
) AS tots