Count Function in query

[tjstromquist]

I have the following fields:

FiscalYr


FiscalWk
SKU
Store_Nbr
Qty_Sold

I want to include a field that shows the count of store numbers with qty_sold > 0 each week. I don't need to see the actual store number so I will remove that from the query.
I tried multiple ways and cannot get it to work, what am I missing?


Result set should look like the following:

FiscalYr	FiscalWk	SKU	StoreCount
2012	7	315222	1201
2012	8	31522	1245

[jzwp11]

Welcome to the forum!

I assume that your raw data looks something like this:


FiscalYr|FiscalWk|SKU|Store_Nbr|Qty_Sold
2012|1|323943|1|10
2012|1|323943|2|50
2012|1|399399|1|22
2012|2|323943|1|99
2012|2|399399|2|25


Your query would need to group by fiscal year, fiscal week and SKU and then sum the Qty_Sold

SELECT FiscalYR, FiscalWk, SKU, SUM(Qty_Sold) as SumOfQty_Sold
FROM tablename
GROUP BY FiscalYr, FiscalWk, SKU