February 16, 2012 at 6:32 am
I have a CASE Statement but one of the WHEN Outputs does not produce the entire result.
WHEN ResCalc.DESC IN('Other','Other Causes') THEN 'Other'
I get the following result.
Count DESC Amount
2Other-43600
12Other-883749
For some reason it is not collapsing 'Other' and 'Other Causes' into one.
Does anyone have any ideas as to why this could be happening or have a substitute to the CASE Statement?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 6:37 am
Without seeing the query and table structure, I'd guess you're not grouping properly.
SELECT
count(*) totalsRecords,
CASE WHEN ResCalc.DESC IN('Other','Other Causes') THEN 'Other' END Desc,
sum(Amount) amount
FROM <table>
GROUP BY CASE WHEN ResCalc.DESC IN('Other','Other Causes') THEN 'Other' END
something like that?
February 16, 2012 at 7:18 am
Thanks for the reply.
This is what the CASE looks like:
SELECTCOUNT(ResCalc.CONVNO) AS RecordCount,
CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END AS ACCDESC,
SUM(ResCalc.OUTSTANDING) AS SumOutstanding
FROM TEMPOPEN AS ResCalc
WHERE ResCalc.OUTSTANDING <0
GROUP BY ResCalc.ACCDESC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 7:24 am
as calvo stated, you'll need to include the complete case statement in your group by.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 7:35 am
sturner (2/16/2012)
...The probability of survival is inversely proportional to the angle of arrival
I like it and have another one:
Exit from unexitable situation is usually located at the same place as entrance.
February 16, 2012 at 7:46 am
Eugene Elutin (2/16/2012)
sturner (2/16/2012)
...The probability of survival is inversely proportional to the angle of arrival
I like it and have another one:
Exit from unexitable situation is usually located at the same place as entrance.
heheh... good one. 🙂
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 7:51 am
sturner (2/16/2012)
as calvo stated, you'll need to include the complete case statement in your group by.
I tried it and I get a syntax error.:-D
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 8:02 am
What query have you tried?
Have you copied CASE statement together with AS [ColumnName]?
If so remove "AS [ColumnName]" from GROUP BY
February 16, 2012 at 8:07 am
Eugene Elutin (2/16/2012)
What query have you tried?Have you copied CASE statement together with AS [ColumnName]?
If so remove "AS [ColumnName]" from GROUP BY
I tried it and it did not work.
Thanks for the suggestion.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 8:07 am
Welsh Corgi (2/16/2012)
sturner (2/16/2012)
as calvo stated, you'll need to include the complete case statement in your group by.I tried it and I get a syntax error.:-D
bet you left the "AS ACCDESC" on the end of it. remove that and you should be okay
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 8:08 am
I was able to solve the problem by using a simple CTE.
Thanks for the suggestions.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 8:12 am
Welsh Corgi (2/16/2012)
Eugene Elutin (2/16/2012)
What query have you tried?Have you copied CASE statement together with AS [ColumnName]?
If so remove "AS [ColumnName]" from GROUP BY
I tried it and it did not work.
Thanks for the suggestion.
hmmm. I wonder if it is because of the table alias? Try removing it from the case, like this:
CASE
WHEN ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END
I don't remember ever using the format: tableAlias.ColumName format in a group by maybe its an oddity or a bug.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 8:12 am
sturner (2/16/2012)
Welsh Corgi (2/16/2012)
sturner (2/16/2012)
as calvo stated, you'll need to include the complete case statement in your group by.I tried it and I get a syntax error.:-D
bet you left the "AS ACCDESC" on the end of it. remove that and you should be okay
I removed the AS ACCDESC and I still got the error.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 8:15 am
I revised my code and created a simple CTE and that solved the problem.
WITH CTE
AS
(SELECTResCalc.CONVNO,
CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END AS ACCDESC,
ResCalc.OUTSTANDING
FROM TEMPOPEN AS ResCalc
)
SELECTCOUNT(CLMCONVNO) AS [CLAIM COUNT],
ACCDESC,
SUM(OUTSTANDING) AS [UNDER RESERVED]
FROM CTE
GROUP BY ACCDESC
ORDER BY ACCDESC
Thanks for the suggestions.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 8:24 am
I just ran a test on a big table that had a varchar() column with several different values in it. I did a count(*) and used a case like yours in the select statement and in the group by and it worked fine.
I tried it both ways, using a table alias and not using an alias and it worked either way.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply