Sports(2)
Education(3)
Community(0)
Something(0)

SELECT
    AC.ACID, MAX(AC.Name) as Name,
    COUNT(*) as Frequency
FROM
    Album A
INNER JOIN
    AlbumCategories AC ON A.ACID = AC.ACID
GROUP BY
    AC.ACID

Sports(2)
Education(3)

ACID    Name
1       Sports
2       Education
3       Community
4       Something

AID     ACID    Name
1       1       Footbal season 2015
2       1       Footbal season 2016
3       2       Album one
4       2       Album Two
5       2       Album Three

PID     AID Image

CREATE TABLE AlbumCategories
    ([ACID] int, [Name] varchar(9))
;

INSERT INTO AlbumCategories
    ([ACID], [Name])
VALUES
    (1, 'Sports'),
    (2, 'Education'),
    (3, 'Community'),
    (4, 'Something');


CREATE TABLE albums
    ([AID] int, [ACID] int, [Name] varchar(19))
;

INSERT INTO albums
    ([AID], [ACID], [Name])
VALUES
    (1, 1, 'Footbal season 2015'),
    (2, 1, 'Footbal season 2016'),
    (3, 2, 'Album one'),
    (4, 2, 'Album Two'),
    (5, 2, 'Album Three');

SELECT AC.ACID,
       AC.NAME       AS NAME,
       Count(A.ACID) AS Frequency
FROM   albums A
       RIGHT JOIN AlbumCategories AC
               ON A.ACID = AC.ACID
GROUP  BY AC.ACID,
          AC.NAME

╔══════╦═══════════╦═══════════╗
║ ACID ║   NAME    ║ Frequency ║
╠══════╬═══════════╬═══════════╣
║    1 ║ Sports    ║         2 ║
║    2 ║ Education ║         3 ║
║    3 ║ Community ║         0 ║
║    4 ║ Something ║         0 ║
╚══════╩═══════════╩═══════════╝