TSQL calculate the number of records-CodePudding

I have 3 tables with relationships sets between them. Everything works fine.

Table Producent:

ProducentID (PK, int)
ProducentName (nvarchar)

Table Produced:

ProducentID (PK, FK, int)
FilmID (PK, FK, int)

Table Film:

FilmID (PK, int)
FilmName (nvarchar)

Each producent produced various number of films.

I need to generate the following output:

ProducentName | NumberOfProducedFilms

For example:

ProducentName | NumberOfProducedFilms
-------------------------------------
Peter P.      | 2
John J.       | 4
Michael M.    | 7
Edward E.     | 3

CodePudding user response：

You should use count and group by as follows

SELECT P.producentname,
       Count(filmName) NumberOfProducedFilms
FROM   producent P
       JOIN produced PD
         ON P.producentid = PD.producentid
       JOIN film F
         ON PD.filmid = F.filmid
GROUP  BY P.producentname

CodePudding user response：

Thank you @RF1991 for the hint!

This is the full answer:

SELECT P.ProducentName, COUNT(PD.FilmID) NumberOfProducedFilms FROM Producent P
JOIN Produced PD ON P.ProducentID = PD.ProducentID
JOIN Film F ON PD.FilmID = F.FilmID
GROUP BY P.ProducentName
ORDER BY P.ProducentName ASC