Count times ID appears in a table and return in row.

Question

SELECT
Boats.id, Boats.date, Boats.section, Boats.raft, 
river_company.company, river_section.section AS river 
FROM Boats 
INNER JOIN river_company ON Boats.raft = river_company.id 
INNER JOIN river_section ON Boats.section = river_section.id 
ORDER BY Boats.date DESC, river, river_company.company

Returns everything I need. But how would I add a [Photos] table and count how many times Boats.id occurs in it and add that to the returned rows.

So if there are 5 photos for boat #17 I want the record for boat #17 to say PhotoCount = 5

Answer 1

I presume boatsId is a column in the Photos table? The easiest way would be with a subselect:

SELECT ...whatever columns...,

(SELECT COUNT(*) FROM Photos WHERE boatsId = Boats.id)

FROM ...rest of your query

Answer 2

You could add a LEFT JOIN to a sub query as follows:

LEFT JOIN (SELECT COUNT(p.id) num, p.boatID FROM photos p GROUP BY p.boatID) 
          sub_photos ON (sub_photos.boatID = Boats.id)

And then reference sub_photos.num in your SELECT fields.

It would look something like this:

SELECT     Boats.id, Boats.date, Boats.section, Boats.raft, 
           river_company.company, river_section.section AS river,
           sub_photos.num AS number_of_photos
FROM       Boats 
INNER JOIN river_company ON Boats.raft = river_company.id 
INNER JOIN river_section ON Boats.section = river_section.id 
LEFT JOIN  (SELECT COUNT(p.id) num, p.boatID FROM photos p GROUP BY p.boatID) 
           sub_photos ON (sub_photos.boatID = Boats.id)
ORDER BY   Boats.date DESC, river, river_company.company

Answer 3

SELECT Boats.id,
       Boats.date,
       Boats.section,
       Boats.raft,
       river_company.company,
       river_section.section AS river
       (SELECT count(*) FROM Photos WHERE boatsID = Boats.id)
FROM Boats
INNER JOIN river_company ON Boats.raft = river_company.id 
INNER JOIN river_section ON Boats.section = river_section.id 
ORDER BY Boats.date DESC, river, river_company.company

Give this one a try.

Answer 4

Another way:

SELECT 
Boats.id, Boats.date, Boats.section, Boats.raft,  
river_company.company, river_section.section AS river  
Count(Photos.ID) AS PhotoCount
FROM Boats  
INNER JOIN river_company ON Boats.raft = river_company.id  
INNER JOIN river_section ON Boats.section = river_section.id  
OUTER JOIN Photos ON Boats.id = Photos.BoatID ORDER BY Boats.date DESC, river, river_company.company 
GROUP BY
    Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section

Answer 5

You haven't provide any information regarding the structure of the Photos table but something like this should work for you.

SELECT
Boats.id, Boats.date, Boats.section, Boats.raft, 
river_company.company, river_section.section AS river,
PhotoCount.NumberOfBoats as PhotoCount 
FROM Boats 
INNER JOIN river_company ON Boats.raft = river_company.id 
INNER JOIN river_section ON Boats.section = river_section.id 
Left Join
(
    Select b2.Id, Count(b2.Id) as NumberOfBoats
    From Boats b2
    Join Photos p on b2.Id = p.BoatId
    Group By b2.Id
)BoatCount on BoatCount.Id = Boats.Id

ORDER BY Boats.date DESC, river, river_company.company

Answer 6

Another way

SELECT DISTINCT
Boats.id, Boats.date, Boats.section, Boats.raft,  
river_company.company, river_section.section AS river,
count(photos.boatid) OVER(PARTITION BY photos.boatid)
FROM Boats  
INNER JOIN river_company ON Boats.raft = river_company.id  
INNER JOIN river_section ON Boats.section = river_section.id
LEFT JOIN photos on photos.boatid = Boats.id
ORDER BY Boats.date DESC, river, river_company.company