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

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

HY,

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

Thanks in advance,
Ender

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.