get the count of records - 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

SQL Server Development (2000)

get the count of records

Author

Topic

itissidhu
Starting Member

3 Posts

Posted - 2004-08-10 : 03:11:28

hi all,

I have two tables named Master and Child. The structure is as follows:

master
--------
columnName Type
--------------------
categoryId - int
categoryName - nvarchar(50)

Sampledata
-----------
categoryId categoryName
-----------------------------
1 - A
2 - B
3 C

child
---------
columnName Type
--------------------
subCategoryId - int
subCategoryName - nvarchar(50)
categoryId - int

The master table categoryid can have ane number of rows in the child table.

SubCategoryId subCategoryName categoryId
-----------------------------------------
1 - XXX - 1
2 - YYY - 1
3 - ZZZ - 2
4 - AAA - 2
5 - BBB - 2

The output i want is categoryName from the master table and the count from the child table. If there is no rows for the corresponding category then the count should be 0

Sample output

categoryName Count
-----------------------
A - 2
B - 2
C - 0

pls help me how to achieve this

thankx

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-10 : 04:23:51

I'm a little confused with your tables here but I think this should do it:

SELECT CategoryName, IsNull(COUNT(*), 0) AS [Count]
FROM master a LEFT OUTER JOIN child b
ON a.CategoryID = b.CategoryID
GROUP BY CategoryName
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 00:27:13

quote:
Originally posted by Lumbago

I'm a little confused with your tables here but I think this should do it:

SELECT CategoryName, IsNull(COUNT(*), 0) AS [Count]
FROM master a LEFT OUTER JOIN child b
ON a.CategoryID = b.CategoryID
GROUP BY CategoryName
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

no need of isnull() as countreturns 0 if it doesnt find any matching values in child table

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-15 : 23:51:29

declare @master Table (categoryId int,categoryName varchar(32))
insert into @master
select 1, 'A' union all
select 2, 'B' union all
select 3, 'C'

declare @details Table (SubCategoryId int,subCategoryName varchar(32), categoryId int)
insert into @details
select 1, 'XXX', 1 union all
select 2, 'YYY', 1 union all
select 3, 'ZZZ', 2 union all
select 4, 'AAA', 2 union all
select 5, 'BBB', 2

select m.categoryName,count(d.categoryId) as 'count' from @master m
left join @details d on d.categoryId = m.categoryId
group by m.categoryName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources