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.