Steve,
It’s difficult to get SQL to display zero counts like this because the SQL engine is programmed to work on found rows only. Since you are not finding any rows in the >20 group, SQL simply doesn’t add a row to the resultset. It has nothing to do with the contents of the SELECT. There are 3 possible solutions that immediately come to mind. I’ll list them in recommended order: 1) Get the result much as you show it in your second example but don’t simply @ROWS it. Rather, do a @FOR and loop the number of rows you should have (7). Then in each loop use a test or @FILTER to identify the relevant row in the resultset. If you don’t find one, then draw the zero. 2) Loop like option one, but instead of getting the resultset upfront, simply execute 7 individual SQL queries to get the values. Use No Results to draw the zero. 3) Create a Stored Procedure that would build a temporary table with the 7 rows and their values. Then select that whole table back as the result. Here’s some ideas for option 3: http://stackoverflow.com/questions/329477/sql-group-by-day-show-orders-for-each-day Hope that helps. Robert From: Fogelson, Steve [mailto:stevefogel...@askics.net] Sent: Tuesday, April 05, 2011 11:40 PM To: Witango-Talk@witango.com Subject: Witango-Talk: SQL Question - OT SELECT P_Price1, COUNT(*) AS ProductCount FROM categoryproducts c2, products p1 WHERE (c2.Cat_ID=32 AND p1.P_Disable IS NULL) AND (c2.P_ID=p1.P_ID) GROUP BY p1.P_Price1; Price1 Qty 2.99 1 5.99 2 6.49 1 6.99 1 7.49 1 7.99 5 8.99 2 9.99 2 14.99 1 65.99 1 SELECT P_Price1, COUNT(*) AS ProductCount FROM categoryproducts c2, products p1 WHERE (c2.Cat_ID=32 AND p1.P_Disable IS NULL) AND (c2.P_ID=p1.P_ID) GROUP BY p1.P_Price1 > 5, p1.P_Price1 > 10, p1.P_Price1 > 20, p1.P_Price1 > 30, p1.P_Price1 > 50, p1.P_Price1 > 75, p1.P_Price1 > 100 ; 2.99 1 6.49 14 14.99 1 65.99 1 It doesn’t display zero for >20, > 30 etc. I tried a number of different syntax, such as placing the expressions in COUNT() and a number of different COUNT()s and using HAVING with no luck. Any suggestions? Thanks, Steve Fogelson Internet Commerce Solutions _____ To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body.
