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 |
|
Zath
Constraint Violating Yak Guru
298 Posts |
 Posted - 2007-10-24 : 14:06:19
|
| I have the following that works just fine:SELECT lg.Name, SUM(DATEDIFF(DAY, ch.CreatedDateTime, ch.Deadline)) AS totalTime, Count(ch.CaseID) AS numOfCases FROM CaseHeader AS ch INNER JOIN CaseStatus cs ON cs.CaseStatusID = ch.CaseStatusID INNER JOIN Location l ON ch.LocationID = l.LocationID INNER JOIN locationGroup lg ON l.PrimaryLocationGroupID = lg.locationGroupID GROUP BY lg.NameBut now, I'm trying to add this in the select:lg.locationGroupIDEach lg.Name will have the same lg.locationGroupID.So, if lg.Name = 'myName' then lg.locationGroupID = someNumberBut they all will have the same number.I just can't seem to get the syntax down to add this other field and return it.Thanks,Zath |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-24 : 14:10:28
|
| If you add it to the SELECT you need to add it to the GROUP BY too.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-24 : 14:11:15
|
| Then just add it to the SELECT portion and GROUP BY. If they had different locationGroupIDs, then we'd need to use a derived table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-10-24 : 14:11:41
|
| Yes, I tried that but instead of bring back 2 rows, it loses the group by lg.Name and brings back all 9 rows. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-24 : 14:13:05
|
| You'll need to provide sample data of the problem as well as what you want it to return. Please illustrate your issue with data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-10-24 : 14:14:50
|
| Ok, when I added it to the select and group by, I must have did something like put the wrong field in there.I retried it and it worked ok.LOL - having a VERY bad day and my concentration is shot.Thanks and sorry to trouble.Zath |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-24 : 22:33:29
|
| I know you've solved it, but watch out. If you have duplicates of lg.Name you will now get different results because you've included the ID. It will form a different group where previously there was no distinction. |
|
|
|
|
|
|
|
|
Group By