Selecting the number of items from a distinct list

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)

Selecting the number of items from a distinct list

Author

Topic

DJScrib
Starting Member

2 Posts

Posted - 2005-12-14 : 01:50:03

I have a media database that looks like this

Author - Title
Beatles - Song 1
Beatles - Song 2
Dave - Another Song
Eminem - A rap song
Eminem - Another Rap song
Eminem - and one more
ZZ Top - zz

I want to display a list of each artist and the number of titles for each artist, such as

Beatles - 2
Dave - 1
Eminem - 3
ZZ Top - 1

Now of course I can do a Select Distinct(Author) to get the list of authors, and then do a Select count(*) where author = X on each of those items, but I wanted to know if there is a way to do this in a single SQL statement.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 01:52:07

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

-----------------
[KH]
Learn something new everyday

DJScrib
Starting Member

2 Posts

Posted - 2005-12-14 : 01:54:23

wow easy statement, works great, thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 01:55:00

Select Author, Count(*) from yourTable
Group by Author

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 01:56:39

Didnt see your reply khtan

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 02:02:07

>>Didnt see your reply khtan
the brain is faster than the finger. the finger is faster than the eyes. [:D}

One more post to go

-----------------
[KH]
Learn something new everyday

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 02:12:42

Well. You dont need Order by clause as Group by itself order records ASC by default
You need it when you order it by DESC or order by other column

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 02:15:39

quote:
Originally posted by madhivanan

Well. You dont need Order by clause as Group by itself order records ASC by default
You need it when you order it by DESC or order by other column

Madhivanan

Failing to plan is Planning to fail

Gee, I didn't know this.

That's one more thing i learn today

-----------------
[KH]
Learn something new everyday

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 02:26:03

"You dont need Order by clause as Group by itself order records ASC by default"

Tut! Tut!

BoL:

quote:
Note If the ORDER BY clause is not specified, groups returned using the GROUP BY clause are not in any particular order

Unfortunately 99.999% of the time it will work, so it will catch you out the remaining few percent. I wish there was a "I'm in Dev mode, randomise everything I don't explicitly ORDER BY" so that the absence showed up easily!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 08:27:00

Well. But If you use only one column in the Group by Clause then it will be in ASC Order by default.

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 08:31:39

I don't reckon ... that ain't what BoL says