Multi count in single query - Oracle

I need to count two data in same table for 2 time periods. I tried something like this:

SELECT COUNT(CASE
                 WHEN click_date='2011-06-20' THEN 0
                 ELSE 1
             END) AS ThisDayCount, COUNT(1) ToDayCount
FROM MyTable
WHERE click_date BETWEEN '2011-05-01'AND 2011-06-20;

So basically i want to have count开发者_运维百科 from one day as ThisDayCount and count from 2011-05-01 to 2011-05-20

click_date is a String.

You are almost there, just use SUM instead of COUNT:

select 
    sum(case when click_date='2011-06-20' then 0 else 1 end) as ThisDayCount, 
    count(1) ToDayCount 
from MyTable 
where click_date between '2011-05-01'and 2011-06-20;

The existing solutions are correct, but don't explain why you're seeing this phenomenon. Count in SQL statements returns the number of non-null values in the query. Since your query returns 1 or 0, count will use both values.

This can be resolved by using sum to add the values in the column (as per @Adrian's solution) or by changing the case statement to return null for the records that should not be counted (as per @ypercube's solution).

Both solutions perform roughly the same and will return the same result, so it's really just a matter of personal preference.

SELECT COUNT( CASE
                  WHEN click_date='2011-06-20' THEN 1
                  ELSE NULL
              END ) AS ThisDayCount
     , COUNT(*) AS ToDayCount
FROM MyTable
WHERE click_date BETWEEN '2011-05-01' AND '2011-06-20' ;