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.
Go to Top of Page

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/

Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-02-04 : 06:22:07
I really just need the Grade and Qty columns
Go to Top of Page

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/

Go to Top of Page
   

- Advertisement -