Count and Group by based on 2 tables not working
Posted: Tue Feb 23, 2010 7:48 pm
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
SELECT table1.column1, COUNT(table1.column2) FROM table1, table2 WHERE table1.id = table2.id and table2.column3 = 'M' GROUP BY table1.column1