How do I count fields across tables when some counts may be zero? — OZONE Asylum, home of the Mad Scientists

FAQ

This page's ID: 5677

You are editing "How do I count fields across tables when some counts may be zero?"

Who can edit an FAQ?	Anyone registered may edit an FAQ.

Your User Name:
Your Password:
Login Options:	Remember Me On This Computer

Your Text: Insert Slimies » Insert UBB Code » Close Last Tag \| All Tags UBB Help	This is a tricky one and Stanislav Yordanov has answered this nicely in the comments in the MySQL manual. The answer is to LEFT JOIN from. Say you had a number of articles by single authors and you wanted a quick count of them the you would do something like this: [code] SELECT *, COUNT(artlcle.article_id) AS article_count FROM author LEFT JOIN article ON author.author_id = article.author_id GROUP BY author.author_id[/code] Note: You can also get all the other author information out and use them so you have all your information at the same time - saving you have to do a separate count (further WHERE clauses go between the ON and GROUP BY). -------------------------- Relevant links: [url=http://www.mysql.com/doc/en/Counting_rows.html]3.3.4.8 Counting Rows[/url] ___________________ [internallink=4626]Emperor[/internallink] [small][i](Added by: [url=http://www.ozoneasylum.com/cgi-bin/ubbmisc.cgi?action=getbio&UserName=Emperor]Emperor [/url] on Tue 05-Aug-2003)[/i][/small] [small][i](Edited by: [url=http://www.ozoneasylum.com/cgi-bin/ubbmisc.cgi?action=getbio&UserName=Emperor]Emperor [/url] on Tue 05-Aug-2003)[/i][/small] Loading...
Options:	Enable Slimies Enable Linkwords