Count and Group by based on 2 tables not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Count and Group by based on 2 tables not working

Post by rhecker »

In the following example, column1 can have one of four different values. I want to return the total count for each possible value. This would work fine if I was only using one table, except that I also want to limit the results to only those records that have the value 'M' in a column of a different table. Somehow my syntax is wrong because there are only 600 records in the database, but I am getting a total of over a thousand counts. I'm guessing that my WHERE clause is misplaced. Any help appreciated.

SELECT table1.column1, COUNT(table1.column2) FROM table1, table2 WHERE table1.id = table2.id and table2.column3 = 'M' GROUP BY table1.column1
rahulzatakia
Forum Commoner
Posts: 59
Joined: Fri Feb 05, 2010 12:01 am
Location: Ahmedabad

Re: Count and Group by based on 2 tables not working

Post by rahulzatakia »

Hi,

You can try the below query...

SELECT table1.column1, COUNT(DISTINCT table1.column2) FROM table1 LEFT JOIN table2 ON (table1.id = table2.id) WHERE table2.column3 = 'M' GROUP BY table1.column1
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Count and Group by based on 2 tables not working

Post by rhecker »

Thank you! Your query is exactly right. I appreciate the time you took with my issue.
Post Reply