Count Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Count Question

Author

Topic

ridgedawg
Starting Member

2 Posts

Posted - 2006-03-29 : 19:42:34

I have a table with the following fields:
SUBID
SID
QID
RESP

Here is some sample data:

SUBID = 400
SID = 100
QID = 1
RESP = "TEXT"

SUBID = 401
SID = 100
QID = 1
RESP = ".."

SUBID = 400
SID = 100
QID = 2
RESP = "x"

How would I get the count of all the different values of RESP? I was thinking an outer join. So like in the above example, for QID=2 there would be a count of 1 for any record with "x" and a count of 1 for the blank essesentially. Any help would be greatly appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 20:10:55

[code]select RESP, count(*)
from yourtable
group by RESP[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

ridgedawg
Starting Member

2 Posts

Posted - 2006-03-29 : 20:22:50

quote:
Originally posted by khtan

select RESP, count(*)
from yourtable
group by RESP

This will give you the counts overall but if you look for records for a specific QID then you will not get the count for blanks.

select RESP, count(*)
from yourtable
where SID="100" and QID="2"
group by RESP

will only give me the count for that specific data - I want the count for every record for SID="100" who may or may not have an entry for the specific QID. So as in my example, there are 3 entries - 2 for QID=1 and 1 entry for QID=2.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 20:41:07

can you post more sample data and the expected result ?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-30 : 05:32:54

ridgedawg - It's very hard to understand your question, but I'm guessing this is the kind of thing you're after...

--data
declare @t table (SUBID int, SID int, QID int, RESP varchar(10))
insert @t
          select 400, 100, 1, 'TEXT'
union all select 400, 100, 2, 'x'
union all select 401, 100, 1, '..'
union all select 402, 100, 1, '..'
union all select 402, 100, 2, 'x'
union all select 403, 100, 2, 'x'
union all select 404, 100, 2, '..'
union all select 400, 101, 1, 'a'
union all select 400, 101, 2, 'b'

--calculation
select SID, QID, RESP, count(*) as COUNT
from (
    select a.*, b.RESP
    from
        (select distinct a.SUBID, a.SID, b.QID from @t a, @t b) a --all questions for each SUBID & SID
        left outer join (select * from @t) b on a.SUBID = b.SUBID and a.SID = b.SID and a.QID = b.QID
     ) a --t but with additional 'null' answers
group by QID, SID, RESP
having SID = '100'
order by SID, QID, RESP

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources