I think you want something where you join that table with the question
table (call it Item) and a value table (call it Value) like this:
SELECT idItem, intValue, COUNT(idItem) AS AnsCount
FROM (xrefTable a right join Item i on i.id = a.idItem)
right join Value v on v.id = a.intValue
GROUP BY idItem, intValue
Because of the outer joins, idItem will be non-null when there is a
matching answer, so will contribute to the count. Warning: completely
untested.
David
Jeanne Sarfaty Glazer wrote:
>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
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
In the beginning ColdFusion shipped with Website. Both have only gotten better
http://www.deerfield.com/products/website/
Message: http://www.houseoffusion.com/lists.cfm?link=i:6:1722
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:>
