How can I combine data from multiple rows into only one

[end3r]

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

[urtrivedi]

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

[end3r]

HY,

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

Thanks in advance,
Ender

[urtrivedi]

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.

[end3r]

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