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

Re: How can I combine data from multiple rows into only one 80 80
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
Re: How can I combine data from multiple rows into only one 80 80

HY,

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

Thanks in advance,
Ender

Re: How can I combine data from multiple rows into only one 80 80

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.

Re: How can I combine data from multiple rows into only one 80 80

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 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.