Hi Everyone, I have a 3-column cross-reference SQL-Server 2000 table that holds polling data. Column 1 is idUser (id of the user taking the poll) Column 2 is idItem (id of the poll question) Column 3 is intValue (the value selected by the user answering the poll question. it will be 0-5)
The poll is rather long (~100 questions) and I want to create a single SQL query to use for each poll question that will tell me A) How many people answered the question, B) How many people answered "X" to the question (where X is 0,1,2,3,4 or 5) I can do easily do this using a bunch of separate queries (see below), but I'm hoping there's a more elegant solution. Running ONE query 100 times will be annoying enough, I'd reeeeally like to avoid running SEVEN queries 100 times! :-) I've been messing around with CROSS JOINs, but not getting my expected result set. I think I'm just too close to see it. Any insight from you SQL gurus would be greatly appreciated! Thanks so much, - - - Jeanne SELECT COUNT(*) AS TotalResponding FROM xrefTable WHERE idItem = 1 SELECT COUNT(intValue) AS Count0 FROM xrefTable WHERE idItem = 1 AND intValue = 0 SELECT COUNT(intValue) AS Count1 FROM xrefTable WHERE idItem = 1 AND intValue = 1 SELECT COUNT(intValue) AS Count2 FROM xrefTable WHERE idItem = 1 AND intValue = 2 SELECT COUNT(intValue) AS Count3 FROM xrefTable WHERE idItem = 1 AND intValue = 3 SELECT COUNT(intValue) AS Count4 FROM xrefTable WHERE idItem = 1 AND intValue = 4 SELECT COUNT(intValue) AS Count5 FROM xrefTable WHERE idItem = 1 AND intValue = 5 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://affiliates.macromedia.com/t.asp?id=2439&p=go/dr_text_aff1 Message: http://www.houseoffusion.com/lists.cfm?link=i:6:1721 Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
