Help counting rows

Question

Help counting rows

mbSanDiego

Grasshopper

Points: 10
More actions
March 21, 2015 at 12:19 pm

#316344

I need some help with a select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up. I am obviously not an SQL guy, but I would really appreciate some help.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

mbSanDiego (3/21/2015)
I need some help with a select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up. I am obviously not an SQL guy, but I would really appreciate some help.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor

Quick question, can you post DDL for the source tables and consumable (insert statement) sample data? Makes it a lot easier to answer the question.

😎

mbSanDiego Grasshopper Points: 10 More actions · Answer 2

Thanks for your interest, but cam up with the solution:

sum(cast(TrailerFull as int))

Thanks

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

mbSanDiego (3/21/2015)
I had to add each column being selected into a Group by to make it work, but I do not know why.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor

If Car.CarId is PK or unique, then it's safe to refactor the query this way.

SELECT c.CarId, max(c.CarName) as CarName, min(c.CarColor) as CarColor, COUNT(t.TrailerId) as trailerCount, ...

FROM Car c

LEFT JOIN Trailer t on t.CarId = c.CarId

GROUP BY c.CarId

Not a great improvment but at least you needn't edit GROUP BY every time you need more/less columns from Car.