November 27, 2007 at 4:00 am
Good day,
I'm encountering a problem with the following select statements posted below:
select case when count(column1) =0 then 0 else sum(value) end as 'test'
from table1
where column3 = 0
and data <= '2007-09-30'
and column4 = 145214
The number of rows on column1 is 0, so the result will be
test
0
If I try the same select statement, but grouped on column no. 4, I get no results, whatsoever.
select column4, case when count(column1) =0 then 0 else sum(valoare) end as 'test'
from table1
where column3 = 0
and data <= '2007-09-30'
and column4 = 145214
group by column4
From my point of view, the logical output would be:
column4 test
145214 0
But of course, my logic is flawed somehow as the query doesn't return anything. I have encountered this problem in the past and I don't have a solution for it yet.
I need the output to be exactly like my 2nd example.
If anyone could shed some light into this matter, I'd be eternally grateful.
Thanks,
Val
Va multumesc,
V
November 27, 2007 at 4:26 am
This is because the table table1 does not have any rows matching the WHERE filter.
I don't know why you're grouping on a filtered column:cool:
--Ramesh
November 27, 2007 at 4:31 am
It's just an example that is meant to point out that particular exception.
I need to get back those rows as well
That little query is part of a much bigger one. I could explain why I would want those to show, but I don't think that'll help with the solution I'm after.
if it does, I'll gladly explain why i need those rows.
November 27, 2007 at 5:09 am
I think you need to explain the larger context of this problem of yours.
While you're at it, please provide some example data that demonstrates the problem, and the expected output from the supplied example data.
An explanation in just words that describes the logic around the problem would also be helpful.
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply