Sum (Total) - 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

General SQL Server Forums

New to SQL Server Programming

Sum (Total)

Author

Topic

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-22 : 15:47:24

I need to sum the totals for each of the ratings, then group them by owneridname. the problem i am having is that the "Count(*) as total" returns a total that includes ratings that are not listed below as a criteria.

Eg. the total for all ratings for a user john would be 789.
that figure would inlcude amounts for a rating eg. dead. that is not in the list below. please help.

SELECT owneridname, SUM(CASE WHEN new_ratingname = 'Hot' THEN 1 ELSE 0 END) AS Hot,
SUM(CASE WHEN new_ratingname = 'warm' THEN 1 ELSE 0 END) AS Warm, SUM(CASE WHEN new_ratingname = 'cold' THEN 1 ELSE 0 END) AS Cold,
SUM(CASE WHEN new_ratingname = 'cool' THEN 1 ELSE 0 END) AS Cool, SUM(CASE WHEN new_ratingname = 'new' THEN 1 ELSE 0 END) AS New,
SUM(CASE WHEN new_ratingname = 'Reservation Holder' THEN 1 ELSE 0 END) AS [Reservation Holder],
SUM(CASE WHEN new_ratingname = 'site visit' THEN 1 ELSE 0 END) AS [Site Visit], SUM(CASE WHEN new_ratingname IS NULL THEN 1 ELSE 0 END)
AS [Not Rated], SUM(CASE WHEN new_ratingname = 'Continous Updates' THEN 1 ELSE 0 END) AS [Cont Updates],
SUM(CASE WHEN new_ratingname = 'worked tasks' THEN 1 ELSE 0 END) AS [Wkd Tasks], COUNT(*) AS Total
FROM FilteredContact AS filteredcontact
WHERE (statuscodename = 'active')
GROUP BY owneridname

Melvin Felicien
IT Manager
DCG Properties Limited

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-22 : 16:15:12

The only criteria you have is:

statuscodename = 'active'

If you want to only return and count rows with those different ratingname values, you need to filter for those rows in your WHERE clause.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-22 : 16:53:24

Replace 'COUNT(*) AS Total' with:

SUM(CASE WHEN new_ratingname = 'Hot' 
		OR new_ratingname = 'warm' 
		OR new_ratingname = 'cold' 
		OR new_ratingname = 'cool' 
		OR new_ratingname = 'new' 
		OR new_ratingname = 'Reservation Holder' 
		OR new_ratingname = 'site visit' 
		OR new_ratingname IS NULL 
		OR new_ratingname = 'Continous Updates' 
		OR new_ratingname = 'worked tasks' THEN 1 ELSE 0 END) AS Total

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-22 : 22:33:13

Thank you guys this is PERFECT. thank you for your help

Melvin Felicien
IT Manager
DCG Properties Limited

hey001us
Posting Yak Master

185 Posts

Posted - 2007-05-22 : 23:35:14

SUM(CASE WHEN new_ratingname IN ('Hot', 'warm', 'cold', 'cool', 'new', 'Reservation Holder'
, 'site visit', 'Continous Updates', 'worked tasks') OR new_ratingname IS NULL
THEN 1 ELSE 0 END) AS Total
The above is less code but don’t know it’s fast or not

hey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources