The number of votes for each programming language is determined with SUM(IF(...)) : SUM « Aggregate Functions « SQL / MySQL

1. SQL / MySQL
2. Aggregate Functions
3. SUM

The number of votes for each programming language is determined with SUM(IF(...))

      
mysql>
mysql>
mysql> CREATE TABLE mytable (
    ->   id int(11) NOT NULL default '0',
    ->   choice tinyint(4) NOT NULL default '0',
    ->   ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO mytable VALUES (1,4,'2003-01-14 15:46:18'),
    ->                                 (2,1,'2003-01-14 15:49:44'),
    ->                                 (3,4,'2003-01-14 15:49:50'),
    ->                                 (4,4,'2003-01-14 15:49:53'),
    ->                                 (5,4,'2003-01-14 15:49:54'),
    ->                                 (6,2,'2003-01-14 15:49:58');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql> SELECT DATE_FORMAT(ts, '%Y-%m') AS mnth,
    -> SUM(IF(choice=1, 1, 0)) AS c,
    -> SUM(IF(choice=2, 1, 0)) AS java,
    -> SUM(IF(choice=3, 1, 0)) AS perl,
    -> SUM(IF(choice=4, 1, 0)) AS php,
    -> SUM(IF(choice=5, 1, 0)) AS vb,
    -> SUM(IF(choice=6, 1, 0)) AS other,
    -> COUNT(*)
    -> FROM mytable
    -> GROUP BY mnth;
+---------+------+------+------+------+------+-------+----------+
| mnth    | c    | java | perl | php  | vb   | other | COUNT(*) |
+---------+------+------+------+------+------+-------+----------+
| 2003-01 |    1 |    1 |    0 |    4 |    0 |     0 |        6 |
+---------+------+------+------+------+------+-------+----------+
1 row in set (0.00 sec)

mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
    
    
  

Related examples in the same category