mysql count multi columns

Question

I have a video site and I'm storing hit statistics in a table like this:

+------------+------------------+------+-----+-------------------+----------------+
| 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 |                |
+------------+------------------+------+-----+-------------------+----------------+

I want show stats daily (like 2012-02-14: 5000 web views, 850 iphone views), like this:

+---------------------+---------+---------------+
| 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           |
+---------------------+---------+---------------+

The iPhone user ID is 2422, the others are web users.

I'm sorry for my bad English.

That's basically a cross-tab, and not easy to do with basic sql. Unless you need to see this kind of output in a raw query result, you're better off doing a regular plain 'group by' query and then doing the transformation in your client. — Marc B
– Marc B, Commented Feb 14, 2012 at 19:57
your table does not make sense to me. Please explain the columns. id, date_id, and ip seem obvious, but the other 3 need some explaination. — DwB
– DwB, Commented Feb 14, 2012 at 19:59
What does your output show, daily number of web users and iphone users. or should it be Date, Video_id, web_viewers, IPhone_Viewers — Naveen Kumar
– Naveen Kumar, Commented Feb 15, 2012 at 7:50

Bassam Mehanni · Accepted Answer · 2012-02-14 20:27:26Z

2

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)

edited Feb 14, 2012 at 20:27

answered Feb 14, 2012 at 20:00

Bassam Mehanni

14.9k2 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ilmari Karonen · Accepted Answer · 2012-02-14 20:24:44Z

0

If I understood your question correctly, something like this query should do it:

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

You can try it on SQLize.

By the way, if you want to count "unique visitors" (identified by matching user ID, user-agent string and IP address), you can do it with a subquery:

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

(If you didn't need to split the counts between iPhone and non-iPhone users, you could just use COUNT(DISTINCT user_id, ip, user_agent) instead of the subquery.)

edited Feb 14, 2012 at 20:24

answered Feb 14, 2012 at 20:13

Ilmari Karonen

50.5k9 gold badges96 silver badges156 bronze badges

2 Comments

alioygur Over a year ago

no it will not work. Because this sum user_id im want sum rows.

Ilmari Karonen Over a year ago

In MySQL, the value of a Boolean expression like user_id = 2422 is 1 if the condition is true and 0 if it is false. So SUM(user_id = 2422) indeed counts the number of rows that have user_id equal to 2422.

Collectives™ on Stack Overflow

mysql count multi columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related