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
Count and Group by based on 2 tables not working
Moderator: General Moderators
-
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
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
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
Thank you! Your query is exactly right. I appreciate the time you took with my issue.