GROUP BY...WHAT?!? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY...WHAT?!?

Author

Topic

matt_barr
Starting Member

2 Posts

Posted - 2009-02-25 : 12:12:30

Hi,

I'm a bit of a newbie to SQL, and am having problems when trying to combine 'CASE' with 'GROUP BY'.

The statement below works, but what I'm trying to do is have COUNT(Bug.BugID) and SUM(Bug.IfNewRequest) then grouped by the first column. All attempts to find how the first column should/could be grouped have failed so far!

SELECT
CASE
WHEN Bug.CurrentOwner > -1 THEN CONVERT(varchar, Login.FName + ' ' + Login.LName)
WHEN Bug.CurrentOwner < -1 THEN CONVERT(varchar, Groups.GroupName)
ELSE 'unassigned'
END AS Owner,
Bug.BugID,
Bug.IfNewRequest
FROM SWISEDB.dbo.Bug Bug
LEFT JOIN SWISEDB.dbo.LogIn LogIn ON Bug.CurrentOwner=Login.PersonID
LEFT JOIN SWISEDB.dbo.Groups Groups ON Groups.GroupID=-(Bug.CurrentOwner + 10) AND Groups.ProjectID = Bug.ProjectID
WHERE Bug.ProjectID=12 AND Bug.IfClosed=0

Any advice?

Thanks,
Matt

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 12:18:47

If you can provide some sample data...it'll be easier.

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-25 : 12:19:32

This should work:

SELECT
CASE WHEN Bug.CurrentOwner > -1 THEN CONVERT(varchar, Login.FName + ' ' + Login.LName)
WHEN Bug.CurrentOwner < -1 THEN CONVERT(varchar, Groups.GroupName)
ELSE 'unassigned'
END AS Owner,
count(Bug.BugID) as num_bugID,
sum(Bug.IfNewRequest) as sum_newRequest
FROM SWISEDB.dbo.Bug Bug
LEFT JOIN SWISEDB.dbo.LogIn LogIn ON Bug.CurrentOwner=Login.PersonID
LEFT JOIN SWISEDB.dbo.Groups Groups ON Groups.GroupID=-(Bug.CurrentOwner + 10) AND Groups.ProjectID = Bug.ProjectID
WHERE Bug.ProjectID=12 AND Bug.IfClosed=0
group by
CASE WHEN Bug.CurrentOwner > -1 THEN CONVERT(varchar, Login.FName + ' ' + Login.LName)
WHEN Bug.CurrentOwner < -1 THEN CONVERT(varchar, Groups.GroupName)
ELSE 'unassigned'
END

I assume your problem was probably simply that you left the "as Owner" on the end of the case statement. You need to remove that part when putting it in the Group By clause.

Also, I don't know what your whole situation is, but you may want to know that you could add "distinct" inside your SUM and COUNT if necessary.

e.g. count(distinct bug.bugID) as count_bugID,
sum(bug.ifNewRequest) as sum_newRequest

If for instance, your join returns the same bugID within a group multiple times, you may be counting the same bug more than necessary.

Just a thought when testing your results.

Hope this helps.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 12:19:43

[code]Select Owner,Count(BugID),SUM(IfNewRequest)
from
(SELECT
CASE
WHEN Bug.CurrentOwner > -1 THEN CONVERT(varchar, Login.FName + ' ' + Login.LName)
WHEN Bug.CurrentOwner < -1 THEN CONVERT(varchar, Groups.GroupName)
ELSE 'unassigned'
END AS Owner,
Bug.BugID,
Bug.IfNewRequest
FROM SWISEDB.dbo.Bug Bug
LEFT JOIN SWISEDB.dbo.LogIn LogIn ON Bug.CurrentOwner=Login.PersonID
LEFT JOIN SWISEDB.dbo.Groups Groups ON Groups.GroupID=-(Bug.CurrentOwner + 10) AND Groups.ProjectID = Bug.ProjectID
WHERE Bug.ProjectID=12 AND Bug.IfClosed=0)M
Group by Owner
Order by Owner[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 12:27:45

Specify length for varchar as well

.

quote:
Originally posted by matt_barr

Hi,

I'm a bit of a newbie to SQL, and am having problems when trying to combine 'CASE' with 'GROUP BY'.

The statement below works, but what I'm trying to do is have COUNT(Bug.BugID) and SUM(Bug.IfNewRequest) then grouped by the first column. All attempts to find how the first column should/could be grouped have failed so far!

SELECT
CASE
WHEN Bug.CurrentOwner > -1 THEN CONVERT(varchar(n), Login.FName + ' ' + Login.LName)
WHEN Bug.CurrentOwner < -1 THEN CONVERT(varchar(n), Groups.GroupName)
ELSE 'unassigned'
END AS Owner,
Bug.BugID,
Bug.IfNewRequest
FROM SWISEDB.dbo.Bug Bug
LEFT JOIN SWISEDB.dbo.LogIn LogIn ON Bug.CurrentOwner=Login.PersonID
LEFT JOIN SWISEDB.dbo.Groups Groups ON Groups.GroupID=-(Bug.CurrentOwner + 10) AND Groups.ProjectID = Bug.ProjectID
WHERE Bug.ProjectID=12 AND Bug.IfClosed=0

Any advice?

Thanks,
Matt

matt_barr
Starting Member

2 Posts

Posted - 2009-02-25 : 12:34:13

Thanks everyone (I can't believe how quickly you all rallied round!)

There's enough to get me going again there - sodeep and SQLforGirls, your examples both worked straight off.

I'll remember to include data where possible for next time!

Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources