How to get the number of each unique items?

Posted by typingcat on October 28, 2005, 7:43 pm

Please log in for more thread options

Hello.
I'd like to know the SQL query for the following operation.
Table
AUTHOR BOOK
Bill A
Bill B
Charles A
Bill C
....

Output
Bill : 3 books
Charles : 1 book

I know that there is a function to get the number of matching field but
in this case I don't know what authors are in this table. Maybe I
should,
1)Get each unique authors
2)Query the number of matching items using author's name.

I don't know how to do the 1 and is there any better way? Thank you.

Posted by Aggro on October 29, 2005, 11:13 am

Please log in for more thread options

typingcat@gmail.com wrote:
show/hide quoted text

select AUTHOR,count(*) as books from yourtablename group by AUTHOR;

Posted by Aggro on October 29, 2005, 11:19 am

Please log in for more thread options

typingcat@gmail.com wrote:

show/hide quoted text

That is a very, very wrong and slow way to use database. If you need to
use loop in your program to make several queries, you are going to have
very poor performance and usually the same thing can be done with one
quick query. ( Sometimes it is faster to make multiple queries, instead
of one, but you really should know what you are doing if you do that.
And when that is the case, the database is most likely very complex and
queries need to interact with several different tables. )

I already told you how to do this with single query, but if you need to
get unique authors you could:
select AUTHOR from yourtablename group by AUTHOR;