Using CASE WHEN with COUNT - 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)

Using CASE WHEN with COUNT

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-13 : 10:01:57

I need to check and see if there are multiple dates here and if there aren't then return the date, but if there are return the word various.

CASE WHEN COUNT(vew_DSG_BlackBookDetail.dDistribution) = 1 THEN vew_DSG_BlackBookDetail.dDistribution ELSE 'Various' END

I tried it like this, but get back the following error:

Didn't expect COUNT after the SELECT column list.

Any help would be greatly appreciated.

Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 10:16:58

Maybe it's better to see the complete query?

E 12°55'05.25"
N 56°04'39.16"

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-13 : 10:35:47

[code]SELECT TOP 100 PERCENT pkInvestmentId AS InvestmentID, sCRMAccountName AS Security, dInitialContribution AS DateACQ, '1' AS Quantity,
sCRMContactFullName, SUM(nContributionAmt) - SUM(nDistributionAmt_Recall) AS UnitPrice, SUM(nContributionAmt) - SUM(nDistributionAmt_Recall)
AS BuyAmount, CASE WHEN COUNT(vew_DSG_BlackBookDetail.dDistribution)
= 1 THEN vew_DSG_BlackBookDetail.dDistribution ELSE 'Various' END AS DateSold
FROM dbo.vew_DSG_BlackBookDetail vew_DSG_BlackBookDetail
GROUP BY pkInvestmentId, sCRMAccountName, dInitialContribution, dDistribution, sCRMContactFullName
ORDER BY sCRMAccountName[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-13 : 10:45:38

Works for me

USE Northwind
GO

SELECT OrderDate, CASE WHEN COUNT(*) > 1 THEN 'Multiples' ELSE 'Single' END AS Orders
FROM Orders
GROUP BY OrderDate

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 11:11:31

dprichard -- maybe if you show us the actual error message, that might be helpful?

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

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-13 : 11:26:07

I have been messing around with it and moved it over to SQL Enterprise manager and it gave me this error:

Syntax Error Converting Datetime from Character String.

There is either a date in that field or Null. There may be NULL, then two dates and I need to say if the total of the fields that have dates is one show the date if it is more than one then show various. I think the NULL fields may be throwing it off from what I have been reading. Could that be causing this error?

Thanks again for any help on this!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 11:38:31

There does not appear to be any datetime conversions or columns anywhere in the code you have posted. You keep posting vague snippets of code that have nothing to do with the errors you are receiving. You need to start being very specific if you want any one to be able to help you. We can't read your mind and we don't have crystal balls that let us magically see all of your code and that tells us what you are trying to do.

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

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-13 : 11:45:56

This is the entire error. The rest looked irrelevant so my apologies. The entire query is posted above. Sorry if I appear to be being vague, but it isn't giving me anymore data than this. The date field is the dDistribution I am trying to get the count on.

[Microst][ODBC SQL SERVER DRIVER][SQL SERVER]Syntax Error Converting Datetime from Character String.

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 11:52:49

WHERE are you running this query?
In SSMS or the view designer or what?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 12:01:51

You appear to be selecting from a view -- are you sure that the view is properly written and works?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 12:06:48

CASE WHEN requires all return fields to be of same type. I guess you've to cast your datefield to varchar while returning to remove this error.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 13:08:29

Maybe?:

CASE WHEN COUNT(vew_DSG_BlackBookDetail.dDistribution) = 1 THEN CAST(vew_DSG_BlackBookDetail.dDistribution AS VARCHAR(20) ELSE 'Various' END

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-03-13 : 13:10:56

Lampry and visakh16 thank you that worked!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-13 : 22:20:53

Off topic, but:

SELECT TOP 100 PERCENT ?

You don't want that. If you are trying the old SQL2000 trick of forcing a view to have a sort order, you're SOL in 2005 I'm afraid.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources