SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sum select case..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

509 Posts

Posted - 02/04/2013 :  06:02:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3591 Posts

Posted - 02/04/2013 :  06:07:26  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/04/2013 :  06:20:03  Show Profile  Reply with Quote
are you looking at only aggregate data or do you need detail as well along with count?

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

Go to Top of Page

magmo
Aged Yak Warrior

509 Posts

Posted - 02/04/2013 :  06:22:07  Show Profile  Reply with Quote
I really just need the Grade and Qty columns
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/04/2013 :  06:26:49  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000