Group By Issues - 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 2000 Forums

Transact-SQL (2000)

Group By Issues

Author

Topic

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-26 : 17:20:57

Hello. I've looked around the internet for 2 full days now on how to get certain data I'm pulling from a SQL database into a datagrid the way that I want it. Basically, I'm pulling all data from several tables with a couple where clauses and then binding it to a datagrid and displaying it. The data is basically information from our internal company's time/hour keeping web program. Some columns include: date, hours, comments, etc. The way it is now is all of the relevent information is displayed in the datagrid with a footer row having the total amount of hours.

The problem with this is that there can be multiple entries of hours for each day. I want to rollup the hours for each day and display that either as a subtotal in the datagrid or in a whole new datagrid. Meaning that I would have a row with one date that has the total amounts of hours entered for that day, then the next day and total hours, and so on and so forth.

I've tried doing this through my SQL statements with a group by and having clause but I can't have ALL of the Select data in the group by clause. I only want the date in the group by and then the sum of the hours for that day. I've also tried putting multiple Select statements together and almost have what I want, but not quite. I'm not sure how I could get a Rollup statement to work or not...

Here's the original SQL query displaying everything without any group by:

Select * From Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID where Hours.EmpID ='" & EmpID & "' and Hours.complete='N' order by Hours.wdate ASC

Here's what I've gotten to so far that doesn't pull all data but at least gets close to displaying the data pulled as I want:

Select wd.wdate, empid, complete, (Select SUM(whours) From Hours where wdate=wd.wdate and complete='N') AS whours From hours AS wd where empid='" & EmpID & "' and complete='N' order by wdate ASC

What I really want is the following statement but because of the damn group by clause, it wont let me:

Select *, SUM(Hours.whours) From Clin JOIN Hours ON Clin.Clin = Hours.Clin JOIN Task ON Hours.TaskKey = Task.TaskKey JOIN Employee ON Hours.EmpID = Employee.EmpID where Hours.EmpID ='" & EmpID & "' and Hours.complete='N' Group By Hours.wdate order by Hours.wdate ASC

Can anyone help me with fixing my SQL syntax so that I get the data in the format I require? Maybe there's an entirely differnt way to do this that I'm unaware of? I'm sorry for the long post, but I thought as much information as possible would be best. Thanks! :-)

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-27 : 11:23:34

Is there no one who can help?

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-27 : 12:32:24

To add more to this for a better explanation:

I am using SQL Server 2000, not mySQL (a datagrid is part of ASP.NET). I know about trying not to use the "evil select star" but I came to this web program with it already being partially built, and thus I'm improving it though without trying to break it, so I'm try to change as little as possible.

I realize the "Select *, SUM(Hours.whours)..." is incorrect but I was trying to illustrate what I WANTED to do, not neccessarily correct syntax. If I wanted correct syntax, I would have had a VERY long select statement, so I instead placed a *.

I also know that all of the non-aggregates SHOULD be placed in the group by field, but as I said, when I've done that, the results displayed ARE NOT what I want, which is a total of hours per day.

And what I mean by "hours per day" is that each entry has a date and the amount of hours worked for a certain task. There can be several entries per day and thus by default, the datagrid displays all entries and thus the same day in multiple entries. I want to rollup or subtotal all of the hours for a SINGLE day, even though a single day may span over several entries. Thus, I want to do a Group By for the field wdate and also sum the total hours which is the field whours. This is where all of the problems occur, as I can't get the syntax to work as I need it to.

I hope this further explanation can help with your suggestions. Thanks again!

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 12:59:01

I can't really tell what you want/need and I'm not patient enough to study the novel you published

Give us your exact DDL or enough of it to illustrate your problem. Add enough DML (sample data) also to illustrate the objective. Finally, your desired output based on the sample data provided.

Here is a link to detailed instructions on how to provide this information.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-27 : 13:18:27

OK, hopefully this will help:

Users enter their work time every day into a web program. The information received by the program is the date, task, hours, and other information. There can be multiple entries of hours per date/day due to different tasks used or something. I want to output the sum of the hours entered grouped by a single day (if there's only one task). Otherwise, if there's more than one task, output a subtotal of the hours for the group of entries for a single day.

Sample data would be (much less info here than what's in the program):

Employee Date Task Hours
John 4/25/2006 Doc Spec 4
John 4/25/2006 Doc Spec 3
John 4/26/2006 IT 2
John 4/26/2006 IT 1
John 4/27/2006 Doc Spec 4
John 4/27/2006 IT 4

Would like this output or similar:

Employee Date Task Hours
John 4/25/2006 Doc Spec 7
John 4/26/2006 IT 3
John 4/27/2006 Doc Spec 4
John 4/27/2006 IT 4

You can see the SQL code in my first post and why I can't do just a simple group by, because of too much extra information being pulled. Please let me know any suggestions you have. Thank you. :)

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 13:26:45

Any column you are aggregating must NOT appear in the group by clause. Every column you are selecting MUST appear in the group by clause. So for you sample (untested because you didn't provide valid DDL/DML)

select employee, Date, Task, sum(Hours) as SumHours
from <whateverTheTableNameIs>
group by employee, Date, Task

Is you date column a datetime column and Do the dates include all different times?

If so, use: dateadd(day, datediff(day, 0, [Date]), 0)
for both the select and the group by.

Be One with the Optimizer
TG

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-27 : 13:33:22

Thanks for the reply, but that goes against the whole point of me posting in the first place. If I don't use the Select * portion of my SQL statement, then I would have to put in about 30 different columns to select and about 29 of those in the Group By clause. Even when I do that (I've tried) it still doesn't group by the date, everything just looks like the same as if I didn't have the group by clause at all. That's why I'm trying to look for a different solution, maybe an inner select statement or something where a group by clause isn't required. Do you understand what I mean? Thanks. :)

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 13:45:26

Did you see that bit I posted about dates? (my last post)

Also, I hope you are running this in Query Analzyer so you can see unformatted dates.

Be One with the Optimizer
TG

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-27 : 13:47:12

The dates in the database are as I posted (like 4/26/2006) and has nothing to do with time or datetime.

For my real query, the following works for the most part:

SELECT UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc, SUM(whours) AS whours FROM Clin C JOIN Hours H ON C.Clin = H.Clin JOIN Task T ON H.TaskKey = T.TaskKey JOIN Employee E ON H.EmpID = E.EmpID Group By UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc Having H.EmpID='" & EmpID & "' AND H.complete='N' ORDER BY H.wdate ASC

But, a vital piece of information is missing, which is the user's "comment", which is basically just what they entered for what they did that day for that task. If I put the "comment" in on the Select statement and then in the Group By, I completely lose the grouping by date as I originally wanted, as each comment is different, so of course it will show all entries. I want to be able to kind of "ignore" the comment in the group by clause, if at all possible, which is why I'm looking for a different solution. :)

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 14:03:33

Ahh...the ole' "But, a vital piece of information is missing"

leave comment out of the select and the group by. If there are 3 rows for the same employee, date, and task all with different comments, which comment would you want to see along with the sum of hours?

Be One with the Optimizer
TG

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-27 : 14:12:36

I need to see all comments, which is why I want it in the select part but NOT in the group by part. That's the whole problem right there. I can't just have any one comment visible... I know it looks impossible, but I'm still hoping there's a way. :)

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

LittlBUGer
Starting Member

19 Posts

Posted - 2006-04-27 : 14:23:57

As an update, I got the following statement to work for the most part, but now I just need to get it formatted in the DataGrid I'm using:

SELECT UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc, whours, comment
FROM Clin C
JOIN Hours H ON C.Clin = H.Clin
JOIN Task T ON H.TaskKey = T.TaskKey
JOIN Employee E ON H.EmpID = E.EmpID
WHERE H.EmpID='" & EmpID & "' AND H.complete='N'

UNION ALL

SELECT UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc, SUM(whours), 'Total'
FROM Clin C
JOIN Hours H ON C.Clin = H.Clin
JOIN Task T ON H.TaskKey = T.TaskKey
JOIN Employee E ON H.EmpID = E.EmpID
WHERE H.EmpID='" & EmpID & "' AND H.complete='N'
GROUP BY UserName, wdate, H.EmpID, Complete, ProjectName, ProjectID, PhaseName, TaskName, ClinDesc
HAVING COUNT(*) > 1

ORDER BY 2, 10

If I can fix the way it looks in my DataGrid, then I should be set! :)

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources