Sum select case.. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sum select case..

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-02-04 : 06:02:46

Hi

I have the following Query....



SELECT        CASE WHEN (dbo.WeightLength.UserMidjeMatt) < (dbo.RangeValues.ValueRangeStart) 
                         THEN dbo.RangeValues.UnderValueChartDisplay WHEN (dbo.WeightLength.UserMidjeMatt) BETWEEN (dbo.RangeValues.ValueRangeStart) AND 
                         (dbo.RangeValues.ValueRangeEnd) THEN dbo.RangeValues.InbetweenValuesChartDisplay WHEN (dbo.WeightLength.UserMidjeMatt) 
                         > (dbo.RangeValues.ValueRangeEnd) THEN dbo.RangeValues.OverValueChartDisplay END AS 'Grade', dbo.Customers.Name, dbo.SurveyAnswerInfo.FackID, 
                         dbo.Users.GenderID, dbo.SurveyAnswerInfo.AvdId
FROM            dbo.Gender INNER JOIN
                         dbo.Users ON dbo.Gender.GenderID = dbo.Users.GenderID INNER JOIN
                         dbo.SurveyAnswerInfo ON dbo.Users.UserID = dbo.SurveyAnswerInfo.UserID INNER JOIN
                         dbo.WeightLength ON dbo.SurveyAnswerInfo.SurveyAnswerInfoID = dbo.WeightLength.SurveyAnswerInfoID INNER JOIN
                         dbo.RangeValues ON dbo.WeightLength.MidjeMattHealthID = dbo.RangeValues.HealthID INNER JOIN
                         dbo.Customers ON dbo.SurveyAnswerInfo.CompanyID = dbo.Customers.CustID
GROUP BY dbo.WeightLength.UserMidjeMatt, dbo.SurveyAnswerInfo.CompanyID, dbo.RangeValues.ValueRangeStart, dbo.RangeValues.ValueRangeEnd, 
                         dbo.SurveyAnswerInfo.DateAdded, dbo.Users.GenderID, dbo.SurveyAnswerInfo.FackID, dbo.SurveyAnswerInfo.UserAge, dbo.Customers.Name, 
                         dbo.RangeValues.UnderValueChartDisplay, dbo.RangeValues.InbetweenValuesChartDisplay, dbo.RangeValues.OverValueChartDisplay, 
                         dbo.SurveyAnswerInfo.AvdId
HAVING        (dbo.SurveyAnswerInfo.DateAdded BETWEEN CONVERT(DATETIME, '2012-01-01', 102) AND CONVERT(DATETIME, '2013-09-02', 102)) AND 
                         (dbo.SurveyAnswerInfo.CompanyID = 149)

this Query give me a result like this....



Grade	  Name
Normal	  HC	
Normal	  HC	
Normal	  HC	
Normal	  HC	
NA        HC	
NA        HC	
DA	  HC

I would like to sum the different grades so it look like this instead...



Grade	  Name	Qty
Normal	  HC	4
NA        HC	2
DA	  HC	1

What do I need to change to get it like that?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 06:07:26

The short-cut would be to wrap the existing query as a subquery and use a group by clause like this:


SELECT Grade, Name, COUNT(*) as Qty FROM
(
  --- your original query here
)s
GROUP BY Grade, Name

But, if you need only those 3 columns, you can rewrite the query to simplify it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 06:20:03

are you looking at only aggregate data or do you need detail as well along with count?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-02-04 : 06:22:07

I really just need the Grade and Qty columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 06:26:49

then this would do


SELECT        CASE WHEN (dbo.WeightLength.UserMidjeMatt) < (dbo.RangeValues.ValueRangeStart) 
                         THEN dbo.RangeValues.UnderValueChartDisplay WHEN (dbo.WeightLength.UserMidjeMatt) BETWEEN (dbo.RangeValues.ValueRangeStart) AND 
                         (dbo.RangeValues.ValueRangeEnd) THEN dbo.RangeValues.InbetweenValuesChartDisplay WHEN (dbo.WeightLength.UserMidjeMatt) 
                         > (dbo.RangeValues.ValueRangeEnd) THEN dbo.RangeValues.OverValueChartDisplay END AS 'Grade', dbo.Customers.Name, COUNT(*) AS Qty
FROM            dbo.Gender INNER JOIN
                         dbo.Users ON dbo.Gender.GenderID = dbo.Users.GenderID INNER JOIN
                         dbo.SurveyAnswerInfo ON dbo.Users.UserID = dbo.SurveyAnswerInfo.UserID INNER JOIN
                         dbo.WeightLength ON dbo.SurveyAnswerInfo.SurveyAnswerInfoID = dbo.WeightLength.SurveyAnswerInfoID INNER JOIN
                         dbo.RangeValues ON dbo.WeightLength.MidjeMattHealthID = dbo.RangeValues.HealthID INNER JOIN
                         dbo.Customers ON dbo.SurveyAnswerInfo.CompanyID = dbo.Customers.CustID
GROUP BY CASE WHEN (dbo.WeightLength.UserMidjeMatt) < (dbo.RangeValues.ValueRangeStart) 
                         THEN dbo.RangeValues.UnderValueChartDisplay WHEN (dbo.WeightLength.UserMidjeMatt) BETWEEN (dbo.RangeValues.ValueRangeStart) AND 
                         (dbo.RangeValues.ValueRangeEnd) THEN dbo.RangeValues.InbetweenValuesChartDisplay WHEN (dbo.WeightLength.UserMidjeMatt) 
                         > (dbo.RangeValues.ValueRangeEnd) THEN dbo.RangeValues.OverValueChartDisplay END, dbo.Customers.Name
HAVING        (dbo.SurveyAnswerInfo.DateAdded BETWEEN CONVERT(DATETIME, '2012-01-01', 102) AND CONVERT(DATETIME, '2013-09-02', 102)) AND 
                         (dbo.SurveyAnswerInfo.CompanyID = 149)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources