CODEWITHSUNDEEP
sql
sean717
sean717

Reputation: 12663

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

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,

Upvotes: 2

Views: 6705

Answers (5)

Ian Holing
Ian Holing

Reputation: 119

IMO GROUP BY should be the solution, something like this works for me even with other table joins:

SELECT meetings.id, meetings.location, meetings.date, COUNT( users.id ) AS attendees
FROM  `meetings`
LEFT JOIN users ON meetings.id = users.meeting_id
WHERE meetings.moderator_id =  'XXX'
GROUP BY meetings.id

Upvotes: 0

JonH
JonH

Reputation: 33163

    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

Upvotes: 2

Jose Basilio
Jose Basilio

Reputation: 51498

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

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453563

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

Upvotes: 1

Jesse Dhillon
Jesse Dhillon

Reputation: 7997

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

Upvotes: 0

Related Questions