How to write this query to display a COUNT with other fields

Question

I have following two tables:

Person {PersonId, FirstName, LastName,Age .... } 
Photo {PhotoId,PersonId, Size, Path}

Obviously, PersonId in the Photo table is an FK referencing the Person table.

I want to write a query to display all the fields of a Person , along with the number of photos he/she has in the Photo table.

A row of the result will looks like

24|Ryan|Smith|28|6

How to write such query in tsql?

Thanks,

Answer 1

select Person.*, count(PhotoId) from Person left join Photo on Person.PersonId = Photo.PersonId

Answer 2

SELECT P.PersonId, FirstName, LastName,Age, COUNT(PhotoId) AS Num
FROM Person P 
     LEFT OUTER JOIN PHOTO PH ON P.PersonId =  PH.PersonId
GROUP BY P.PersonId, FirstName, LastName,Age

Answer 3

    SELECT 
       p.PersonId, 
       p.FirstName, 
       p.LastName, 
       p.Age,
       CASE WHEN 
            t.ThePhotoCount IS NULL THEN 0 ELSE t.ThePhotoCount END AS TheCount
       --the above line could also use COALESCE
   FROM 
       Person p 
   LEFT JOIN
         (SELECT 
             PersonId,
             COUNT(*)  As ThePhotoCount
         FROM 
             Photo 
         GROUP BY PersonId) t
    ON t.PersonId = p.PersonID

Answer 4

You need a subquery in order to avoid having to repeat all the columns from Person in your group by clause.

SELECT
  p.PersonId,
  p.FirstName,
  p.LastName,
  p.Age,
  coalesce(ph.PhotoCount, 0) as Photocount
FROM
  Person p
  LEFT OUTER JOIN 
     (SELECT PersonId, 
      COUNT(PhotoId) as PhotoCount 
      FROM Photo 
      GROUP BY PersonId) ph
  ON p.PersonId = ph.PersonId