join with a group by?
i have a table called rc_language_type_table with:
id language
1 english
2 Xhosa
3 afrikaans
etc then i have a table rc_language_type_assoc_table with:
profile_id | language_type_id |
+------------+------------------+
| 3 | 1 |
| 13 | 1 |
| 15 | 1 |
| 16 | 1 |
where i have profiles and each profile is connected to a language id in a 1 to many so then i did:
select *,count(*) from rc_language_type_assoc_table group by language_type_id;
+------------+------------------+----------+
| profile_id | language_type_id | count(*) |
+------------+------------------+----------+
| 3 | 1 | 96 | 开发者_如何学JAVA
| 3 | 2 | 19 |
| 3 | 3 | 18 |
| 64 | 4 | 51 |
| 94 | 5 | 10 |
| 37 | 6 | 26 |
| 3 | 7 | 21 |
| 3 | 8 | 4 |
| 3 | 9 | 6 |
| 88 | 10 | 4 |
| 3 | 11 | 3 |
+------------+------------------+----------+
what i want now is: instead having the language_type_id i want to display the actual language...how would i do this please???
i tried:
select *, count(*)
from rc_language_type_assoc_table, rc_language_type_table
group by language_type_id
where rc_language_type_assoc_table.language_type_id = rc_language_type_table.id;
but i get a syntax error... please help??
thank you
GROUP BY should be "after" the WHERE statement and not before
select *, count(*)
from rc_language_type_assoc_table, rc_language_type_table
where rc_language_type_assoc_table.language_type_id = rc_language_type_table.id
group by language_type_id ;
精彩评论