Mysql query for better sorting of result

The mysql table that I'm using has a structure:

   id    |    type   |   date
--------------------------------
  101    |     1     | 2011-02-08
  102    |     2     | 2011-02-08
  103    |     2     | 2011-02-08
  104    |     2     | 2011-02-07
  105    |     1     | 2011-02-07
  105    |     1     | 2011-02-07

What I want to do is create a query that will give the following result:

  total  type 1  |  t开发者_运维问答otal  type 2  |   date   
------------------------------------------------
       1         |        2        | 2011-02-08
       2         |        1        | 2011-02-07

I tried with following query but not getting the desired result

SELECT count(DISTINCT id) as total, date, type FROM my_table WHERE type !='0' GROUP BY date, type ORDER BY date DESC

How can I do that? Thanks for all suggestions

---

Use:

  SELECT t.date,
         SUM(CASE WHEN t.type = 1 THEN 1 ELSE 0 END) AS total_type_1,
         SUM(CASE WHEN t.type = 2 THEN 1 ELSE 0 END) AS total_type_2
    FROM YOUR_TABLE t
   WHERE t.type != 0
GROUP BY t.date
ORDER BY t.date DESC

I'm assuming the type column is numeric, not string based like the single quotes suggest. Change to suit if that's not the case.

---

Try this:

SELECT type, COUNT(*) as total_type, date FROM my_table WHERE type != '0' GROUP BY type ORDER BY date DESC

---

What you're asking for is pivot functionality, which I guess mysql isn't great at.

If it works for you, I'd suggest altering the output to:

 type  count   date
 ====  =====   ====
 1     1       2011-02-08
 ....

and rearrange at application logic. It would probably also make your SQL more db-independent.