Counting in SQL

Question

AFDELING = DEPARTMENT. MEDLEM = MEMBER. I want to view the number of members of each department, showing the department name, but I can't seem to get it working. All I get is duplicates of Personnr.

SELECT AFDELING.afdnavn, MEDLEM.personnr, count(*)
FROM [dbo].[MEDLEM],[dbo].AFDELING
WHERE AFDELING.afdnr = MEDLEM.afdnr
GROUP BY AFDELING.afdnavn, MEDLEM.personnr
GO

Tables used:

AFDELING
Afdnr  (Primary Key)
Afdnavn

MEDLEM
Afdnr  (foreign key)
Personnr (foreign key)  (Primary key)

Answer 1

I think your mistake is that you are grouping by MEDLEM.personnr. You need to group only by AFDELING.afdnavn and do the count

select
    A.afdnavn,
    count(*) as number
from dbo.AFDELING as A
    inner join dbo.MEDLEM as M on M.afdnr = A.afdnr
group by A.afdnavn

Answer 2

SELECT 
  AFDELING.afdnavn
  , COUNT(MEDLEM.personnr)
FROM
  dbo.AFDELING JOIN
    dbo.MEDLEM ON AFDELING.afdnr = MEDLEM.afdnr
GROUP BY
  AFDELING.afdnavn