Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help counting rows

Author  Topic 

mbarksdale
Starting Member

2 Posts

Posted - 2015-03-21 : 14:12:35

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-21 : 15:45:42
If TrailerFull is type bit:

..., sum(cast(TrailerFull as int))

otherwise:

..., sum(case TrailerFull when 'True' then 1 end)
Go to Top of Page

mbarksdale
Starting Member

2 Posts

Posted - 2015-03-21 : 18:56:36
Thank you, seems to be working great.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-23 : 02:25:11
Change

..., sum(case TrailerFull when 'True' then 1 end)

into

..., sum(case TrailerFull when 'True' then 1 else 0 end)

to avoid unnecessary warning about NULL in Aggregates

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:53:10
unspammed
Go to Top of Page
   

- Advertisement -