count rows of distinct + return detail

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)

count rows of distinct + return detail

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2005-09-25 : 16:39:06

data is like this

rowid, datacategory, dataname, datadesc
1, 'smile', 'happy face', 'blah'
2, 'smile', 'almost happy', 'blah blah'
3, 'frown', 'frown face', 'bl'
4, 'frown', 'fffff', 'ffff'
5, 'frown', 'sdsd', 'sdfsd'

How can I get all the rows out + a count of total of datacategory.

so:

1 smile ....2
2 smile ....2
3 frown.....3
4 frown.....3
5 frown.....3

tnx for the help

slow down to move faster...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-25 : 17:31:22

SELECT A.rowid, A.datacategory, B.total
FROM myTable A INNER JOIN
(SELECT datacategory, count(*) as total FROM myTable GROUP BY datacategory) B
ON A.datacategory=B.datacategory

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources