I’ve been breaking my head over this for the past day and I cant seem to figure it out. Maybe it’s not possible? (please let me know if that’s the case).

Basically I’m trying to get the average sales of each location and then add an extra column that shows me the average sales of the entire dataset. The two requirements separately are easy, the problem is combining them.

Current data set –

What I would like to produce –

— Step 1 trying to get the data without combining (works perfectly)

— Gives you the avg sales by location

SELECT location_id, AVG(sales)
FROM table
WHERE (date > '01/01/2023')
GROUP BY location_id;

— Window function that gives you the total avg time to fill (for all

SELECT location_id, AVG(sales) OVER () AS total_avg
FROM table
WHERE (date > '01/01/2023');

— Step 2 trying to combine – this is where I’m struggling

— Tried combining Aggregate and Window function (not possible)

SELECT location_id, AVG(sales) AS location_avg, AVG(sales) OVER () AS total_avg
FROM table
WHERE (date > '01/01/2023');

— Create a CTE with time to fill by location and then join to window function?
— 2 issues here (1) you cant call a window function in the CTE (a. doesnt work) and 2nd problem is you cant have a where clause in the 2nd function

WITH CTE AS (
SELECT location_id AS CTE_location_id, AVG(sales) AS avg_sales_by_location
FROM table
WHERE (date > '01/01/2023')
GROUP BY CTE_location_id
)

SELECT a.location_id, a.(AVG(sales) OVER ()) AS total_avg, b.avg_time_to_fill
FROM table a
WHERE (date > '01/01/2023')
JOIN CTE b
ON b.CTE_location_id = a.location_id;

>Solution :

You can achieve the desired result by using a subquery or a Common Table Expression (CTE) to calculate the overall average sales and then joining it with the grouped average sales by location. Here’s how you can write the SQL query to get the expected output:

-- Step 1: Get the average sales by location
SELECT location AS Location, AVG(sales) AS AvgSales
FROM table_name
WHERE date > '01/01/2023'
GROUP BY location;

-- Step 2: Get the average sales for the entire dataset (overall average)
SELECT AVG(sales) AS Dataset_Avg
FROM table_name
WHERE date > '01/01/2023';

-- Step 3: Combine the results from Step 1 and Step 2 using a subquery or CTE
WITH AvgSalesByLocation AS (
  SELECT location AS Location, AVG(sales) AS AvgSales
  FROM table_name
  WHERE date > '01/01/2023'
  GROUP BY location
)

SELECT 
  A.Location,
  A.AvgSales,
  B.Dataset_Avg
FROM AvgSalesByLocation A
CROSS JOIN (
  SELECT AVG(sales) AS Dataset_Avg
  FROM table_name
  WHERE date > '01/01/2023'
) B;

Result:

| Location | AvgSales | Dataset_Avg |
|----------|----------|-------------|
| USA      | 4        | 7           |
| France   | 7        | 7           |
| India    | 10       | 7           |

In this query, we first calculate the average sales by location and then calculate the overall average sales in separate queries. Finally, we use a CTE (AvgSalesByLocation) to get the average sales by location and cross join it with the overall average sales to combine the results in a single table. The CROSS JOIN here is used because there is only one row in the overall average result, so it will join with every row of the AvgSalesByLocation CTE.

This will give you the desired output with the average sales for each location and the overall dataset average in the same table.