question

flysql avatar image
flysql asked

sql for counting distinct values

I have one table with three columns : product _no , week_no , outlet_no. data looks like this 345 , 201522 , 98 567 , 201521 , 76 and so on.. I need to calc the no of outlets for each product every week. but they should be uniqe...e.g If a product was sold in outlets A and B in first week the no of stores in first week should be 2 , if it is sold in B , C , D next week the total should be 4 for 2nd week , if it is sold in A and D in third week the total should be still 4 for 3rd week. How do I do this...Any help will be greatly appreciated.
sqlcountdistinct
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Thos expected results don't make much sense for which outlets have sold only during that week. Do you want a cumulative count of outlets that have sold up to and including that week?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
This isn't very nice, but I Think it works... create table #theTable(product_id int, week_no int, outlet_no int); insert #thetable(product_id,week_no,outlet_no) values (1,201501,1), (2,201501,1), (1,201501,2), (1,201502,3), (1,201503,4), (1,201504,1); WITH CTE_product as ( select distinct product_id from #thetable ), CTE_week_no as ( SELECT distinct week_no FROM #thetable ), CTE_outlet_no as ( SELECT distinct outlet_no from #thetable ),CTE_all as ( SELECT product_id, week_no, outlet_no from CTE_product CROSS JOIN CTE_week_no CROSS JOIN CTE_outlet_no ), CTE_IsInOutletForWeek as( select CTE_ALL.*, CASE WHEN t.Product_ID IS NULL THEN 0 ELSE 1 END as ProductIsInOutletForWeek from CTE_ALL LEFT JOIN #theTable t ON CTE_ALL.Product_ID = t.Product_ID AND CTE_ALL.outlet_no = t.outlet_no AND CTE_ALL.week_no = t.week_no ),CTE_IsInOutletForAnyPreviousWeek AS( SELECT product_id, week_no, outlet_no, CASE WHEN (SUM(ProductIsInOutletForWeek) OVER(PARTITION BY product_id, outlet_no ORDER BY week_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))>0 THEN 1 ELSE 0 END AS IsInOutletThisOrPreviousWeek FROM CTE_IsInOutletForWeek )select product_id, week_no, SUM(case when IsInOutletThisOrPreviousWeek=0 then 0 else 1 end) from CTE_IsInOutletForAnyPreviousWeek GROUP BY product_id, week_no order by week_no, product_id; DROP TABLE #theTable;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

flysql avatar image
flysql answered
Thanks for the answers , David , yes I want a cumulative count until the week but I don't want to count the same stores again. Basically customer needs to know how long does it take for a product to reach /sell in all the stores across the country. Magnus , thanks for the answer but its nearly impossible to create the table with weeks and sequential numbers since I have more than three years of data and increasing every week.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

My code won't require sequential numbers. Did you test it on your table instead of #theTable?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.