CASE CONDITION - 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

General SQL Server Forums

New to SQL Server Programming

CASE CONDITION

Author

Topic

ewan
Starting Member

13 Posts

Posted - 2011-12-11 : 22:50:26

I'm creating a SQL QUERY where there is a case condition having two function such as SUM FUNCTION and COUNT FUNCTION. This is my code:

SUM(CASE ItemMaster.Category2 WHEN 'CHICKEN ALC' THEN COUNT(itemMaster.Category2 ) END AS CHICKEN_ALC);

And I got an error saying that "'sum' is not a recognized built-in function name."

What is wrong with my code?
Please I need your help.
Thanks.

jeneca

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-11 : 23:20:20

you can't used an aggregate function ( SUM() ) on another aggregate function ( COUNT() ).

Can you explain what are you trying to achieve here ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 23:45:22

but you can use like this though


SELECT SUM(CASE ItemMaster.Category2 WHEN 'CHICKEN ALC' THEN CntValue END) AS CHICKEN_ALC,
...
FROM
(
SELECT ItemMaster.Category2 ,COUNT(itemMaster.Category2 ) AS CntValue,
....
FROM...

GROUP BY ItemMaster.Category2 ,...
)t

didnt understand why its complaining about sum function though

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

ewan
Starting Member

13 Posts

Posted - 2011-12-12 : 03:58:37

@khtan:

I want to add all the product purchased from the timespan that is in my code. It's like a summary of the purchased product.

@visakh16:

Okay. I will try that code. Thanks.

jeneca

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-12 : 05:32:13

Also as END should be out of SUM

SUM(CASE ItemMaster.Category2 WHEN 'CHICKEN ALC' THEN ...... )) END AS CHICKEN_ALC;

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 09:46:35

quote:
Originally posted by madhivanan

Also as END should be out of SUM

SUM(CASE ItemMaster.Category2 WHEN 'CHICKEN ALC' THEN ...... )) END AS CHICKEN_ALC;

Madhivanan

Failing to plan is Planning to fail

Nope END should be inside and only AS column name should be outside

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

ewan
Starting Member

13 Posts

Posted - 2011-12-13 : 03:47:58

@visakh16:
What I'm going to put on FROM because I have multiple tables to join?
Thanks.

jeneca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 03:55:18

quote:
Originally posted by ewan

@visakh16:
What I'm going to put on FROM because I have multiple tables to join?
Thanks.

jeneca

you need to put the query containing multiple joins inside

so it would be like


SELECT SUM(CASE ItemMaster.Category2 WHEN 'CHICKEN ALC' THEN CntValue END) AS CHICKEN_ALC,
...
FROM
(
Your select query with multiple joins...
GROUP BY ItemMaster.Category2 ,...
)t

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

ewan
Starting Member

13 Posts

Posted - 2011-12-13 : 03:57:29

OKay. Thank you. It's a big help. :)

jeneca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:02:00

welcome

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-15 : 03:14:23

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Also as END should be out of SUM

SUM(CASE ItemMaster.Category2 WHEN 'CHICKEN ALC' THEN ...... )) END AS CHICKEN_ALC;

Madhivanan

Failing to plan is Planning to fail

Nope END should be inside and only AS column name should be outside

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

Yes I wanted to specify it only

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources