Filter not working for a nested agreggate SUM

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 2008 Forums

Transact-SQL (2008)

Filter not working for a nested agreggate SUM

Author

Topic

chorofonfilo
Starting Member

40 Posts

Posted - 2014-05-08 : 18:34:09

Hi people,

I have these two tables (the names have been pluralized for the sake of the example):

Table Locations:

idlocation varchar(12)
name varchar(50)

Table Answers:

idlocation varchar(6)
question_number varchar(3)
answer_text1 varchar(300)
answer_text2 varchar(300)

This table can hold answers for multiple locations according a list of numbered questions that repeat on each of them.

What I am trying to do is to add up the values residing in the answer_text1 and answer_text2 columns, for each location available on the Locations table but for only an specific question and then output a value based on the result (1 or 0).

The query goes as follows using a nested table Answers to perform the SUM operation:

select
l.idlocation,

'RESULT' = (
case when (
select
sum(cast(isnull(c.answer_text1,0) as int)) +
sum(cast(isnull(c.answer_text2,0) as int))
from Answers c
where b.idlocation=c.idlocation and
c.question_number='05'
) > 0 then
1
else
0
end
)

from Locations l, Answers b
where l.idlocation=b.idlocation and b.question_number='05'

In the table Answers I am saving sometimes a date string type of value for its field answer_text2 but on a different question number.

When I run the query I get the following error:

Conversion failed when converting the varchar value '27/12/2013' to data type int

I do have that value '27/12/2013' on the answer_text2 field but for a different question, so my filter gets ignored on the nested select statement after this: b.idlocation=c.idlocation, and its adding up ALL the values hence the error posted.

Could you please tell me what is going wrong here?.

Thank you very much.

Perseverance worths it...:)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-09 : 07:24:43

the ISNUMERIC function is helpful here e.g.


...SUM(CASE WHEN ISNUMERIC(c.answer_text2) = 1 THEN CAST(c.answer_text2 as INT) ELSE 0 END)

chorofonfilo
Starting Member

40 Posts

Posted - 2014-05-09 : 17:20:42

Hi people,

There is an interesting issue on this case, which question I also posted on StackOverflow.

The gbritton suggestion is nice, but I ended up using a case into the length of my text value so I can assure it's a number a nor a date or a longer text:

'RESULT' =
case when (
select sum(
case when len(c.answer_text1) <= 2 then
cast(isnull(c.answer_text1,'0') as int)
else
0
end
) +
sum(
case when len(c.answer_text2) <= 2 then
cast(isnull(c.answer_text2,'0') as int)
else
0
end
)
from Answers c
where c.idlocation=b.idlocation and c.question_number='05'
) > 0
then
1
else
0
end

Also there is an explanation as to why it doesn't obbey my filter on the nested selected which I think is kind of explained on the post put here:

http://stackoverflow.com/questions/23553565/filter-on-a-nested-agreggate-sum-function-not-working

If anyone could provide an extended explanation as to why the filter is not working, I would be thankfull.

Thanks!.

Perseverance worths it...:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources