Counting Unique Rows - 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)

Counting Unique Rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-20 : 09:40:49

RichM writes "I have a table containing 3 columns, say ITEM, CATEGORY, COST.
Is it possible to return the number of each unique row in CATEGORY together with the value of that unique row? I can manage:
SELECT COUNT (*)
FROM tablename GROUP BY category

How can I report the names and quantities of each?"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-20 : 10:29:50

select distinct(category) from tablename as a
will give you a list of 'category' items with duplicates removed
...duplicate in terms of the qualifying information shown.

...select distinct(category), cost from tablename as a
may repeat some 'category' items....where each category item is associated with a difference cost...but the combination category/cost will be unique.

i'm not sure of the syntax...but something like the following might work.

select distinct(category) from tablename as a
inner join
select * from tablename as b
on b.category = a.category

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-20 : 11:40:35

SELECT category, count(category)
FROM tablename
GROUP BY category

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources