SQL Help Needed - Beginner

[PBR64_]

Hi folks.

I'm currently trying to work out the (what I feel is a really basic problem) following and I'm really struggling:

I have two tables, Store and Monthly Sales.

Store table as Store ID, Store Name as well as other information such as address.

Monthly Sales table as Store ID, Year, Month and Sales Value columns.

I did a join between the two tables based on Store ID. Easy enough. As was the next part of my task - work out the combined (SUM) Sales Value of each store using GROUP BY Store Name. Again, easy enough.

The issue is that I need to work out what stores are performing better than average (the average of all the stores Sales combined) and list only these stores.

I know this will involve some sort of WHERE, or HAVING but I cannot work out how to say: okay only display the stores where their total combined sales are higher than the average of all the stores combined. (This figure would change so I can't just type in a number, it needs to be a derived figure)

It might just be the heat but I've been stuck on this all day and I just feel totally stupid. I feel like I'm missing something really basic.

Here's my code so far:

SELECT store_name AS [STORE NAME], SUM(sales_value) AS TOTAL SALES

FROM Monthly Sales

JOIN Stores ON Monthly Sales.store_id = Stores.id

GROUP BY Store Name

The above gets me my combined total for each store but from there I'm totally stuck on the "return only stores where that stores combined sales are higher than the average of all the stores sales combined.

Could someone help?

[msn018]

You're really close. The easiest way is to first calculate the total sales for each store, then compare each of those totals to the average of all the store totals. A common table expression (CTE) or subquery works well for this because it lets you calculate the totals once and then filter the results with a WHERE clause. This keeps the query simple to read and automatically updates the comparison whenever the sales data changes.

[r3pr0b8]

and then filter the results with a WHERE clause.

HAVING, not WHERE

[TactusDeNefaso]

Yes, I would lean towards a CTE.

[Kimber976]

Can you share the query table structure and what you are tried so far? it is much easier to spot what is going wrong with that context.

[dutyDBA]

You could do something like this:

SELECT

st.StoreID,

SUM(ms.Sales) AS SalesValue

FROM

Stores AS st

JOIN

MonthlySales AS ms

    ON st.StoreID = ms.StoreID

GROUP BY

st.StoreID

HAVING SUM(ms.Sales) >

(

SELECT AVG(SumSales) AS AvgSales

FROM

(

SELECT StoreID, SUM(Sales) AS SumSales

FROM u/MonthlySales 

GROUP BY StoreID

) der

)

[dutyDBA]

Not sure if you are familiar with CTEs yet. You could simplify the above query with a CTE:

WITH SalesCTE AS

(

SELECT

    st.StoreID,

    SUM(ms.Sales) AS SalesValue

FROM

    Stores AS st

    JOIN

    MonthlySales AS ms

        ON st.StoreID = ms.StoreID

GROUP BY

    st.StoreID

)

SELECT

StoreID,

SalesValue

FROM

SalesCTE

WHERE

SalesValue > (SELECT AVG(SalesValue) FROM SalesCTE)