SQL procedure to select merge
My procedure is:
create procedure "news"
as开发者_StackOverflow
select newsdate,COUNT(B.id) as total from news B
where B.newsyear < GETDATE()
Group by B.newsdate
select newsdate,COUNT(B.id) as total from news B
where B.status='WAITING' and B.cancel='1'
Group by B.newsdate
Results:
newsdate total
2011 4
2010 8
newsdate total
2011 2
2010 3
How can I merge year totals to obtain this result set:
newsdate total
2011 6 {4 + 2}
2010 11 {8 + 3}
Try this:
select newsdate,COUNT(B.id) as total
from news B
where ( B.newsyear < GETDATE() )
or ( B.status='WAITING' and B.cancel='1' )
Group by B.newsdate
using a simple or statement (if it's indeed the same table):
select newsdate,COUNT(B.id) as total
from news B
where B.newsyear < GETDATE()
or B.status='WAITING' and B.cancel='1'
Group by B.newsdate
or using union all + a sum aggregate (if it's different tables):
select newsdate, sum(total) as total from (
select newsdate,COUNT(B.id) as total from news B where B.newsyear < GETDATE()
Group by B.newsdate
union all
select newsdate,COUNT(B.id) as total from news B where B.status='WAITING' and B.cancel='1'
Group by B.newsdate
) as rows
group by newsdate
精彩评论