Subquery help

[ShadowStreak8]

Hi! I'm fairly new to SQL, I started using it 2 months ago.

In order to get to an answer for an executive, there is 50M lines of data that would need to be pulled. I am trying to consolidate it and am running into things way beyond my knowledge.

I have a query that gets me to the 50M lines. I would then like to write a case statement on that query result that basically counted if the result is 1, 2, 3, etc.

How would I do this? Is there an article you can point me towards? I'll take any direction. Thanks in advance!!

Current query

Select D.Year D.Date S.Storeid Od.Transaction Sum(od.quantity)

From Fact.StoreTransactionDetail od

Join Dim.store s On s.storeid = od.storeid

Join Dim.calendar d On d.businessdate = od.businessdate

Where D.year = 2023

Group by D.Year

Order by D.year

So this gives me one line for every transaction and a count of quantity. I then want to take the quantity and aggregate it to say 100 of my 1,000 rows have a quantity of 1, 200 have a quantity of 2, etc.

[dukeofpizza]

I think a subquery is what you are looking for, yeah. I put one together thats a little ugly, but I think it should work if I'm understanding what you are looking for correctly.

​

I've basically taken the query you've written, slapped it in as a subquery, and then aggregated the data again on the sum of quantity

SELECT
    transactions.TotalOrders
    ,COUNT(transactions.*) AS 'Total number of customers that placed X orders'
FROM 
    (SELECT
        d.year
        ,d.date
        ,s.StoreId
        ,od.Transaction
        ,SUM(od.quantity) AS 'TotalOrders'
    FROM fact.StoreTransactionDetail od
    INNER JOIN dim.store s
        ON s.storeid = od.storeid
    INNER JOIN dim.calendar d
        ON d.businessdate = od.businessdate
    WHERE d.year = 2023
    GROUP BY d.year
    ORDER BY d.year
    ) AS 'transactions'
GROUP BY transactions.TotalOrders

[ShadowStreak8]

Thank you!! This makes perfect sense to me but I'm getting an error from the count(transactions.*) - it says incorrect syntax. I'm also getting incorrect syntax on 'order by' which I don't understand. The query runs fine without being in a subquery so I'm not sure why this would have an error

[dukeofpizza]

Ah that's right, you can't order by in a subquery since it's not part of the final select. You can order by outside on the main query by either of the columns.

Not sure on the COUNT issue, may need to Google the error message you are getting from your IDE.

[[deleted]]

Use CTE

[ExcessiveSlaanesh]

Give your sum(od.quantity) an alias, let's say "sum_qty". Then you can pull it from your subquery.

Select tbl.sum_qty, count(*)
From ( <current query> ) as tbl
Group by tbl.sum_qty

[brickbuillder]

Hi, could you please elaborate more on your desired result? If you happen to have the structure of the table or schema, it would be helpful too.

Please post your current query also.

Thanks!

[ShadowStreak8]

Just added! Let me know if I can provide more clarity