Untitled

mysql count multi columns
+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| user_id    | int(10) unsigned | NO   | MUL | NULL              |                |
| video_id   | int(10) unsigned | NO   | MUL | NULL              |                |
| user_agent | varchar(500)     | NO   |     | NULL              |                |
| ip         | varchar(255)     | NO   |     | NULL              |                |
| date_add   | timestamp        | NO   | MUL | CURRENT_TIMESTAMP |                |
+------------+------------------+------+-----+-------------------+----------------+

+---------------------+---------+---------------+
| date                | web     | iphone        |
+---------------------+---------+---------------+
| 2012-02-09          |    500  | 478           |
| 2012-02-10          |    2377 | 204           |
| 2012-02-12          |    247  | 21            |
| 2012-02-13          |    4879 | 236           |
| 2012-02-14          |    8767 | 101           |
+---------------------+---------+---------------+

SELECT DATE(date_add) AS date,
       SUM(CASE WHEN user_id = 2422 THEN 0 ELSE 1 END) AS Web,
       SUM(CASE WHEN user_id = 2422 THEN 1 ELSE 0 END) AS iPhone
FROM stats
GROUP by DATE(date_add)

SELECT
  DATE(date_add) AS date,
  SUM(user_id != 2422) AS web,
  SUM(user_id = 2422) AS iphone
FROM stats
GROUP BY date

SELECT
  date,
  SUM(user_id != 2422) AS web,
  SUM(user_id = 2422) AS iphone
FROM
  ( SELECT DATE(date_add) AS date, user_id
    FROM stats
    GROUP BY date, user_id, ip, user_agent ) AS foo
GROUP BY date