SQL How to group data by 2 different date columns?

I got a table like this:

    Id     Date1          Date2     Status
    ----------------------------------------------
     1    01/01/2010     null         A
     2    04/04/2010    开发者_JS百科  05/14/10    X
     3    01/01/2010     null         A
     4    01/11/2010      01/01/2010  X
     5    01/02/2010     null         A

And several other records, Date 1 is not null but it is only relevant in the group by if the Status is A, for the records where Date2 is not null, regardless of the status the group by should be by this Date2.

The desired result set is as follows:

 Date             Number of A Status        Number of Date 2 not null statuses
------------------------------------------------------------------------------
 01//01/2010            2                                     1
 01/02/2010             1                                     0
 05/14/2010             0                                     1 

Basically the group by must group by date, the problem is that in some cases it is for the Date1 column and in the other case is for the Date2 columns. How can this be accomplished?

---

You can group by a decode, or case, expression. Only tried in Oracle so not sure if this is portable. With this data:

create table t42 as
select 1 id, to_date('01/01/2010') date1, null date2, 'A' status from dual
union select 2, to_date('04/04/2010'), to_date('05/14/2010'), 'X' from dual
union select 3, to_date('01/01/2010'), null, 'A' from dual
union select 4, to_date('01/11/2010'), to_date('01/01/2010'), 'X' from dual
union select 5, to_date('01/02/2010'), null, 'A' from dual
/

select * from t42;

ID                     DATE1                     DATE2                     STATUS
---------------------- ------------------------- ------------------------- ------
1                      01/01/2010                                          A     
2                      04/04/2010                05/14/2010                X     
3                      01/01/2010                                          A     
4                      01/11/2010                01/01/2010                X     
5                      01/02/2010                                          A

You can do:

select case when date2 is null and status = 'A' then date1
        else date2 end as "Date",
    sum(case when status = 'A' then 1 else 0 end) as "Number of A status",
    sum(case when date2 is null then 0 else 1 end) as "Number of Date 2 null"
from t42
group by case when date2 is null and status = 'A' then date1 else date2 end
order by 1;

Which gives:

Date                      Number of A status     Number of Date 2 null  
------------------------- ---------------------- ---------------------- 
01/01/2010                2                      1                      
01/02/2010                1                      0                      
05/14/2010                0                      1

---

It's a typical PIVOT query:

   SELECT x.date,
          SUM(CASE WHEN 'A' IN (y.status, z.status) THEN 1 ELSE 0 END) AS NumStatusA,
          SUM(CASE 
                WHEN y.date2 IS NOT NULL OR z.date2 IS NOT NULL THEN 1 
                ELSE 0 
              END) AS NumDate2NotNull
     FROM (SELECT a.date1 AS date
             FROM YOUR_TABLE a
           UNION 
           SELECT b.date2 AS date
             FROM YOUR_TABLE b) x
LEFT JOIN YOUR_TABLE y ON y.date1 = x.date
LEFT JOIN YOUR_TABLE z ON z.date1 = x.date
 GROUP BY x.date

But you need to derive a table containing the dates from both columns, based on your data, to join against first.

---

Maybe something like this:

SELECT DISTINCT Date1 as Date,
(SELECT COUNT(*) FROM MyTable WHERE DATE1=MyT.Date1 AND Status = 'A') NumberAStatus,
(SELECT COUNT(*) FROM MyTable WHERE DATE1=MyT.Date1 AND Date2 is not null) NotNullDate2

FROM MyTable MyT