user6247986 user6247986 - 7 months ago 7
SQL Question

Count and group the number of times each town is listed in the table

SELECT PEOPLE.TOWNKEY, TOWN_LOOKUP.TOWN FROM PEOPLE
INNER JOIN TOWN_LOOKUP
ON PEOPLE.TOWNKEY = TOWN_LOOKUP.PK
ORDER BY TOWN


Current Table Output:

enter image description here

Answer

You are missing the group by clause entirely:

SELECT     tl.town, COUNT(*)
FROM       people p
INNER JOIN town_lookup ON p.townkey = tl.pk
GROUP BY   tl.town
ORDER BY   tl.town
Comments