DISTINCT/SUM

[nevets72]

Here's a problem that I thought would be simple, but I can't seem to get it to work.......I've got a temporary table with 2 columns. The first column is Job Code, the second column is Hours billed to that Job Code. There are only 8 different Job Codes, but my table has about 900 rows. I simply want to eliminate all the duplicate Job Code entries, rolling them into one row while summing up all the hours billed to the individual Job Code in question.....however, with my SQL (seen below), I still get 900 rows back. I'd expect to get 8 rows, with all the Hours summed up for each individual Job Code.....I don't understand why I can't get this to work.....any ideas?:

SELECT DISTINCT
Job_Code, SUM(Hours)
FROM
#Temp_Table7
GROUP BY Job_Code, Hours

 

[cmmrfrds]

You shouldn't need the distinct keyword since you are already grouping by job code. Also, the hours don't need to be in the group by since they are already aggregated.

 

[Rishi146]

SELECT DISTINCT
Job_Code, SUM(Hours)
FROM
#Temp_Table7
GROUP BY Job_Code

why are you grouping by hour when you have sumed it in yor select

 

[bperry]

Tried this yet?

SELECT
Job_Code, SUM(Hours) as TotHrs
FROM
#Temp_Table7
GROUP BY Job_Code


bp

 

[nevets72]

Thanks guys....I used the:

SELECT DISTINCT
Job_Code, SUM(Hours)
FROM
#Temp_Table7
GROUP BY Job_Code

and that worked.

 

[Rishi146]

and like cmmrfrds said you dont need the distict clause