Group By Issue 1

[LittlBUGer]

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!

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

http://www.littlbuger.info

 

[Gunnien]

I think the select part of your query is the wrong one inless the select * only returns this value "Hours.wdate". So my suggestion is that your query to:

Select
Hours.wdate,
Other....,
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,
Other....
order by
Hours.wdate ASC

This one should work fine I think. Remember you group by clause has to contain all columns from your selection exept the once you aggregate.

 

[LittlBUGer]

Thanks for your help, but that's exactly what I DON'T want to do. I've already tried that and it doesn't display it like I require at all. Plus the fact that if I went and did the select with each seperate item I need pulled (instead of just using a *), I would have around 30 things to place there PLUS in the group by clause as well as only one of them is aggregated. This is why I asked my question in the first place. I can't do it that way as it doesn't work. Any other suggestions? Thanks!

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

http://www.littlbuger.info

 

[Golom]

Unfortunately, life and SQL aren't fair.

You may WANT to

Select *, SUM(...) ... GROUP BY Hours.wdate

but the rules for grouping state that you cannot use Group By when you have selected all fields using "*".

If you require grouping then you must explicitly list the fields to be selected in SELECT and those to be grouped in GROUP BY.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[r937]

unless you're using mysql, which has no difficulty running a query like --

Select *, SUM(...) ... GROUP BY Hours.wdate

of course, it might not produce the correct results, but it will run



r937.com | rudy.ca

 

[Golom]

Thanks Rudy ... didn't know that since I don't use MySQL.

So you have a choice between blowing up on an error or getting suspect results?

Hmmmm ... is there a door #3 that I can pick?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[r937]

i always advise people to put all non-aggregates into the GROUP BY

and i always advise people never to use the evil "select star"

i just mentioned that mysql "feature" (which they do caution you about, citing the unpredictable results that can occur) because i see a lot of php developers, who seem to use nothing but mysql, completely oblivious to what GROUP BY actually does

however, littlBUGer can't be using mysql, because of (a) the "datagrid", whatever that is, and (b) it won't run the query with the incorrect GROUP BY

littlBUGer, could you explain in words what you want totals for? according to your description,

"I want to rollup the hours for each day"

this suggests that you should be grouping by day

r937.com | rudy.ca

 

[LittlBUGer]

Thanks for your replies.

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

http://www.littlbuger.info

 

[r937]

okay, good stuff

let's go by these specs --

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.

that would be this --

select wdate
     , sum(whours)
  from daTable
group by wdate

r937.com | rudy.ca

 

[LittlBUGer]

OK r937, I understand that. If only it was that simple. But I need to pull in all of the other information as well, as in my original SQL query I posted in my first post. I know doing just what you did works, but then all I have is the date and hours, and none of the other information which is just as important.

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

http://www.littlbuger.info

 

[r937]

okay, now we are getting somewhere!

let's say you had this data --

wdate wfoo whours
2006-04-24 A 1.5
2006-04-24 B 2.0
2006-04-24 C 2.5
2006-04-25 X 2.5
2006-04-25 Y 2.5

which value for wfoo do you want to see on the result rows?

wdate wfoo sum(whours)
2006-04-24 ? 6.0
2006-04-25 ? 5.0


r937.com | rudy.ca

 

[PHV]

You wanted something like this ?
SELECT *,
(Select SUM(whours) From Hours Where wdate=H.wdate And complete='N' And EmpID=H.EmpID) 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
WHERE H.EmpID='" & EmpID & "' AND H.complete='N'
ORDER BY H.wdate ASC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittlBUGer]

Yes, now you understand! OK, so in my case, say for the Task (or wfoo in your example) there was different tasks per day, then I wouldn't require a single day to be displayed, but just the total hours for that task, and then the next task. So there would end up being multiple entries as the results due to different tasks instead of just one entry per day as I originally wanted. This is fine in this case and a subtotal for that group of entries or something would be great.

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

http://www.littlbuger.info

 

[LittlBUGer]

PHV, I'll try that and see if it works...

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

http://www.littlbuger.info

 

[LittlBUGer]

PHV, your suggestions seems ALMOST right on, but I still get the same results as if I didn't do any grouping or sums at all. I think the first "Select *,..." overrides the inner Select and SUM. Though I'm definitely no SQL expert (which is why I'm here) so I don't know what's really going on. I'll try taking out the star and only selecting certain information instead of everything and see what happens. Thanks!

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

http://www.littlbuger.info

 

[LittlBUGer]

PHV, I fiddled with your suggestion more and still can't get it to work. I can get it to display all of the information I need, including the hours totals/sums, but it's still not grouped by day/date. Any more help? Thanks.

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

http://www.littlbuger.info

 

[PHV]

Could you, please, post some input samples, actual SQL code with actual results and expected results ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittlBUGer]

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

http://www.littlbuger.info

 

[PHV]

Like this ?
SELECT *,
(Select SUM(whours) From Hours Where wdate=H.wdate And complete='N' And EmpID=H.EmpID [!]And TaskKey=H.TaskKey[/!]) 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
WHERE H.EmpID='" & EmpID & "' AND H.complete='N'
ORDER BY H.wdate ASC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittlBUGer]

That doesn't do anything to help with grouping by date... it may help later, once I can actually get results grouped by date, but I'm not at that point yet.

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

http://www.littlbuger.info