grouping by one field and doing a sum of two others

[nuct]

Hi,

I have made a view which contains the following:

Year 1month 2month
2002 0 1
2003 0 1
2003 1 0
2002 0 1
1998 0 1
1998 0 1
1985 0 1
2003 1 0

How do I go about grouping by the year field and giving the sum of both the 1month and 2month fields. E.G.


Year 1month 2month
2003 2 4
2002 5 3
etc...


Here is the view:

CREATE VIEW Employment_Period AS
select substring(cast(empstartdate as char),8,4) as "Year",
CASE WHEN datediff(month,empstartdate, empenddate) >= 1 AND datediff(month,empstartdate, empenddate) < 3 THEN '1' ELSE '0' END AS "1MONTH",
CASE WHEN datediff(month,empstartdate, empenddate) >= 3 THEN '1' ELSE '0' END AS "3MONTH"
from tblstaff
where empenddate IS NOT NULL



Any help would be most appreciated.

Simon.

 

[PHV]

Have you tried something like this ?
SELECT E.Year, Sum(E.1month) As Total1, Sum(E.2month) As Total2
FROM Employment_Period E
GROUP BY E.Year


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

 

[nuct]

Yeah, thats what I tried but it didn't work at first because the 1s and 0s in the definition of the view where chars not numbers. Sorted now though.

Cheers

Simon