0

Hy,

I have a select like:

select
case when isnull(my_table.entry,0) > 0 and my_table.edate = convert(datetime,substring('2009-09-20',1,10)) THEN COUNT(*)
			  else 0
END as no_entries_date1
,case when isnull(my_table.entry,0) > 0 and my_table.edate = convert(datetime,substring('2009-09-21',1,10)) THEN COUNT(*)
			  else 0
END as no_entries_date2
,case when isnull(my_table.entry,0) > 0 and my_table.edate = convert(datetime,substring('2009-09-22',1,10)) THEN COUNT(*)
			  else 0
END as no_entries_date3
from my_table

Eventually I get the following result:

no_entries_date1 | no_entries_date2 | no_entries_date3
-------------------------------------------------------------------------------
6 | 0 | 0
0 | 2 | 0
0 | 0 | 5

How can I "merge" the rows into a single one so it would look like:

no_entries_date1 | no_entries_date2 | no_entries_date3
-------------------------------------------------------------------------------
6 | 2 | 5


Best regards,
Ender

2
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by end3r
0
SELECT sum(case when isnull(my_table.entry,0) > 0 
AND my_table.edate = convert(datetime,substring('2009-09-20',1,10)) 
THEN 1  else 0 END) AS no_entries_date1,
sum(case when isnull(my_table.entry,0) > 0 
AND my_table.edate = convert(datetime,substring('2009-09-21',1,10)) 
THEN 1  else 0 END) AS no_entries_date2,
sum(case when isnull(my_table.entry,0) > 0 
AND my_table.edate = convert(datetime,substring('2009-09-22',1,10)) 
THEN 1  else 0 END) AS no_entries_date3
FROM my_table
0

HY,

Thanks for your reply, but it still shows the same result.
Maybe another approach is needed.

Thanks in advance,
Ender

0

Please try the query again, it can not show multiple rows, when you use aggregate function without group by it will always return one or no row.

otherwise post your sample data with column names.

0

Yes. You are right. I used group by. After I removed it the problem dissapeared :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.