How to output multiple columns from one MySQL column?

Question

Consider the following table

tweets
---------------------------------
tweet_id  nyse_date   class  type
---------------------------------
1         2011-03-12  2      0
2         2011-03-12  1      1
3         2011-03-12  1      0
4         2011-03-12  1      0
5         2011-03-12  0      0
6         2011-03-12  1      0
7         2011-03-12  3      2
8         2011-03-12  3      0

Each tweet has assigned a 'class', which is either 1, 2 or 3 and a 'type', being 0, 1 or 2. I want to have an overview of the number of tweets in each class and type, as follows:

nyse_date   class1  class2  class3  type0  type1  type2
-------------------------------------------------------
2011-03-12  3       1       2       6      1      1

I started with the following query (grouping by date because the real table has many different dates):

SELECT
  nyse_date,
  COUNT(class) AS class1 FROM tweets WHERE class = 1,
  COUNT(class) AS class2 FROM tweets WHERE class = 2
GROUP BY
  nyse_date ASC

The expected output was

nyse_date   class1  class2
--------------------------
2011-03-12  3       1

but I got an error instead. I believe this has to do with the fact that I can only use one WHERE clause per query (and I'm using 2) but I don't know what the alternative would be. Do you?

Any help is greatly appreciated :-)

Shlomi Noach · Accepted Answer · 2012-07-14 13:06:55Z

6

Try this:

SELECT
  nyse_date,
  SUM(class = 1) AS class1,
  SUM(class = 2) AS class2,
  SUM(class = 3) AS class3
FROM 
  tweets
GROUP BY
  nyse_date ASC

The class = 1 is a boolean expression, which evalues to TRUE or FALSE. A TRUE equals 1. Hence using SUM practically means "count all rows for which condition holds"

answered Jul 14, 2012 at 13:06

Shlomi Noach

9,4741 gold badge25 silver badges20 bronze badges

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

2 Comments

Pr0no Over a year ago

This is brilliant, thanks :-) With this technique, is it also possible to, for instance, count tweets of type 0 that are in class 1? So basically ...SUM(type=0 AND class=1) AS type0class1?

Shlomi Noach Over a year ago

Indeed so. Go ahead and start playing!

davek · Accepted Answer · 2012-07-14 13:04:24Z

0

Use the GROUP_CONCAT aggregate function in MySql:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

answered Jul 14, 2012 at 13:04

davek

23k11 gold badges80 silver badges97 bronze badges

Comments

james31rock · Accepted Answer · 2012-07-14 13:19:09Z

0

Does this work?

SELECT nyse_date, SUM( IF( class =1, 1, 0 ) ) AS class1, SUM( IF( class =2, 1, 0 ) ) AS class2
FROM tweets
GROUP BY nyse_date

answered Jul 14, 2012 at 13:19

james31rock

2,7152 gold badges22 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to output multiple columns from one MySQL column?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related