SQL Group By Question

[mdr2271]

I am running a Group By query where I count the number of records per category per ID#. What I would like to have my results set show is that if a particular ID does not have a value in a particular category then it will show as 0. For example, I create the following table, populate it and then run the basic query at the end.

------------------------------------------------------
create table #temp1 (
id char(3),
category char(10)
)
go

insert into #temp1 values ('abc', 'Category 1')
insert into #temp1 values ('abc', 'Category 2')
insert into #temp1 values ('abc', 'Category 3')
insert into #temp1 values ('def', 'Category 1')
insert into #temp1 values ('def', 'Category 2')
insert into #temp1 values ('def', 'Category 3')
insert into #temp1 values ('def', 'Category 4')
insert into #temp1 values ('abc', 'Category 1')
insert into #temp1 values ('abc', 'Category 1')
insert into #temp1 values ('abc', 'Category 2')
insert into #temp1 values ('abc', 'Category 3')
insert into #temp1 values ('def', 'Category 4')
insert into #temp1 values ('def', 'Category 3')
go

select id, category, count(*) from #temp1 group by id, category order by id, category
go
--- ---------------------------------------

The result set shows:

id category
abc Category 1 3
abc Category 2 2
abc Category 3 2
def Category 1 1
def Category 2 1
def Category 3 2
def Category 4 2


but I want it to show:

id category
abc Category 1 3
abc Category 2 2
abc Category 3 2
abc Category 4 0
def Category 1 1
def Category 2 1
def Category 3 2
def Category 4 2

Is this possible?

 

[PHV]

You may try this:
SELECT A.id, A.category, COUNT(B.category)
FROM (SELECT DISTINCT X.id, Y.category FROM #temp1 X, #temp1 Y) A
LEFT JOIN #temp1 B ON A.id=B.id AND A.category=B.category
GROUP BY A.id, A.category

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886