CODEWITHSUNDEEP
sqlsql-serverselectcountrow
Landmine
Landmine

Reputation: 1789

Count times ID appears in a table and return in row

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

Upvotes: 1

Views: 5903

Answers (7)

nka
nka

Reputation: 41

Select YourID, COUNT(*) FROM YourTable GROUP BY YourID

This works just fine for when I'm trying to track down where record count might be different based on an ID or any other column.

Upvotes: 1

EMP
EMP

Reputation: 61971

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

Upvotes: 2

Chris Bednarski
Chris Bednarski

Reputation: 3414

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

Upvotes: 1

codingbadger
codingbadger

Reputation: 43974

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

Upvotes: 1

Murph
Murph

Reputation: 10190

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

Upvotes: 1

Ham Vocke
Ham Vocke

Reputation: 2994

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.

Upvotes: 1

Daniel Vassallo
Daniel Vassallo

Reputation: 344301

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

Upvotes: 2

Related Questions