Simple Sum case funtion - 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

Simple Sum case funtion

Author

Topic

biggles2000uk
Starting Member

2 Posts

Posted - 2011-09-21 : 04:18:12

Hi,

i am trying to get this simple queary to run but i keep getting the same error
---------------------------------------
SELECT
`feedatabase`.`Subteam code`,

sum(case when `feedatabase`.`Period` = 201101 then sum( `feedatabase`.`Open matters`) else 0 end) as May10

FROM feedatabase feedatabase

group by
`feedatabase`.`Subteam code`

order by feedatabase.`Subteam code`

-------------------------------------------------
The error message i get is

-------------------------------------------------
Error: Syntax error (missing operator) in query expression 'sum(case when `feedatabase`.`Period` = 201101 then sum( `feedatabase`.`Open matters`) else 0 end)'. (State:37000, Native Code: FFFFF3E4)
--------------------------------------------------

It is from an ODBC connection to a Access database.

Any help will be greatly appreciated

lappin
Posting Yak Master

182 Posts

Posted - 2011-09-21 : 05:13:34

Are you sure you want to do a SUM inside a SUM?
If you do remove outer SUM and test:
SELECT
(case when `feedatabase`.`Period` = 201101 then sum( `feedatabase`.`Open matters`) else 0 end) as May10
FROM feedatabase feedatabase

Or do you really want to:
SELECT
SUM (case when `feedatabase`.`Period` = 201101 then ( `feedatabase`.`Open matters`) else 0 end) as May10
FROM feedatabase feedatabase
group by
`feedatabase`.`Subteam code`

order by feedatabase.`Subteam code`

biggles2000uk
Starting Member

2 Posts

Posted - 2011-09-21 : 05:24:51

hi,

even if i try to do the simple slq below
-----------------------------------------------
SELECT
`feedatabase`.`Subteam code`,

sum(case when `feedatabase`.`Period` = 201101 then 1 else 0 end) as May10

FROM feedatabase feedatabase

group by
`feedatabase`.`Subteam code`

order by feedatabase.`Subteam code`
----------------------------------------------

i still get the same error message
-----------------------------------------------

Error: Syntax error (missing operator) in query expression 'sum(case when `feedatabase`.`Period` = 201101 then 1 else 0 end)'. (State:37000, Native Code: FFFFF3E4)

lappin
Posting Yak Master

182 Posts

Posted - 2011-09-21 : 07:22:18

Try
sum(case when [feedatabase].[Period] = '201101' then 1 else 0 end) as May10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources