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.
| 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 cLEFT JOIN Trailer t on t.CarId = c.CarIdGROUP 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) |
 |
|
|
mbarksdale
Starting Member
2 Posts |
Posted - 2015-03-21 : 18:56:36
|
| Thank you, seems to be working great. |
|
|
|
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 AggregatesMadhivananFailing to plan is Planning to fail |
|
|
|
huangchen
Starting Member
37 Posts |
Posted - 2015-04-02 : 05:53:10
|
| unspammed |
|
|
|
|
|
|
|
|
Help counting rows