Two-column SQL count query with first column having static labels

I am using Oracle 10g and I have a table called downloads which basically looks like this:

ID     DOWNLOAD_DATE
1      2011-02-10
2      2011-03-10
3      2011-04-10
4      2011-05-10
5      2011-01-11
6      2011-04-22
7      2011-02-18
8      2011-03-14
9      2011-02-01
10     2011-01-31

etc.

I would like to execute a query that returns the following:

Downloads today
Downloads yesterday
Downloads from start date until today
Downloads on start date
Downloads on day after start date

I would like to print the results in two 开发者_运维问答columns. In the first column I'd like to put a static label, like the ones above. And in the second column the count.

I've tried something like this:

select count(id) from downloads where download_date = '2007-08-06'
union
select count(id) from downloads where download_date = '2007-08-08'

But this has 2 problems. One, it only prints one column, the count. Two, if there is no data for a given date (or range of dates), nothing is printed. I need a zero to be printed.

So then I tried something like this:

select download_date, count(download_date) from downloads where download_date = '2007-08-06'
group by download_date
union
select download_date, count(download_date) from downloads where download_date > '2007-08-08'
group by download_date

This returns two columns but the first column isn't static, it's a field in the database (download_date). And more importantly, it returns multiple rows for query that selects a range of dates (where download_date > '2007-08-08'), whereas I just want one row, with its count.

How can I do this?

Many thanks, Bob

First of all. Use Union All for this. Union will strip double records, which is probably not what you want in this case. It won't matter much, but union all is faster too. ;)

Second, I don't understand why you can't just put in a static label, like this:

select 'Look, static' from dual

Then, you could write a query like this:

select 
  'Downloads today' as Caption, 
  count(*) as DownloadCount 
from 
  Downloads where trunc(download_date) = trunc(sysdate)
union all
select 
  'Downloads yesterday'
  count(*)
from 
  Downloads where trunc(download_date) = trunc(sysdate) - 1

Count will print 0 if there is no data.

Just put a static label into each of your selects in the union version like this:

SELECT 'some label', ...
 UNION
 ...