Page 1 of 1

Count and Group by based on 2 tables not working

Posted: Tue Feb 23, 2010 7:48 pm
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

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

Posted: Tue Feb 23, 2010 11:45 pm
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

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

Posted: Wed Feb 24, 2010 6:36 am
by rhecker
Thank you! Your query is exactly right. I appreciate the time you took with my issue.