CODEWITHSUNDEEP
sql
hidden
hidden

Reputation: 3236

I need some help here with SQL Sum function group by dates and group

I have a table called Table1

CaseNumber | Year  | Month | Group    | HoursAttended
1          | 2010  |   1   |   16-20  |  8
2          | 2010  |   1   |   16-20  |  1
3          | 2010  |   2   |    0-5   |  2
4          | 2009  |   4   |    0-5   |  2
4          | 2009  |   4   |  16-20   |  5

If I wanted to have a result like this 

Year | Month |GroupHours| GroupHourTotal 
2010 | 1     |16-20     | 9
2010 | 3     |2         | 2
2009 | 4     |0-5       | 2
2009 | 4     |16-20     | 5

I tried this but I am getting incorrect results when the dates are not in the same month. Any ideas?

select Year,Month,group,hourattended,sum(hourattended)
from Table1
groupby Year,Month,hourattended,sum(hourattended)
order by year desc

Upvotes: 0

Views: 216

Answers (4)

Chris B. Behrens
Chris B. Behrens

Reputation: 6295

You have a dependent field, Group, which needs to be broken into a separate table. Once you do that, it's a piece of cake:

SELECT Year, Month, GroupTitle, SUM(HoursAttended) As HoursAttended From Cases C JOIN Groups G ON C.GroupId = G.Id GROUP BY Year, Month, GroupTitle ORDER BY year desc

Replace "Group" with "GroupId", make it a foreign key to a Groups table, and Bob's your uncle.

Upvotes: 1

Eric Mason
Eric Mason

Reputation: 725

Only group by the columns that are not aggregates in the result set. Since you're summing HoursAttended, it does not belong in your GROUP BY.

select Year,Month,group,hourattended,sum(hourattended) from Table1 groupby Year,Month order by year desc

Upvotes: 2

John K.
John K.

Reputation: 5474


select Year,Month,group,sum(hourattended)
from Table1
groupby Year,Monthorder by year desc

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

Don't group by hourattended

select Year,Month,group,sum(hourattended) grouphourtotal
from Table1
group by Year,Month,group
order by year desc, month desc

This must be pseudo, otherwise quote the "group" column name as required

Upvotes: 3

Related Questions