how to group by with a sql subqueries

Question

I can't think clearly at the moment, I want to return counts by station_id, an example of output would be:

station 1 has 3 fb post, 6 linkedin posts, 5 email posts station 2 has 3 fb post, 6 linkedin posts, 5 email posts

So I need to group by the station id, my table structure is

CREATE TABLE IF NOT EXISTS `posts` (
  `post_id` bigint(11) NOT NULL auto_increment,
  `station_id` varchar(25) NOT NULL,
  `user_id` varchar(25) NOT NULL,
  `dated` datetime NOT NULL,
  `type` enum('fb','linkedin','email') NOT NULL,
  PRIMARY KEY  (`post_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=x ;

The query I have so far is returning station 0 as having 2 linkedin posts when it has one (2 in the db tho)

SELECT Station_id, (select count(*) FROM posts WHERE type = 'linkedin') AS linkedin_count, (select count(*) FROM posts WHERE type = 'fb') AS fb_count, (select count(*) FROM posts WHERE type = 'email') AS email_count  FROM `posts` GROUP BY station_id;

Answer 1

Or, the fastest way, avoiding joins and subselects to get it in the exact format you want:

SELECT
  station_id,
  SUM(CASE WHEN type = 'linkedin' THEN 1 ELSE 0 END) AS 'linkedin',
  SUM(CASE WHEN type = 'fb'       THEN 1 ELSE 0 END) AS 'fb',
  SUM(CASE WHEN type = 'email'    THEN 1 ELSE 0 END) AS 'email'
FROM posts
GROUP BY station_id;

Outputs:

+------------+----------+------+-------+
| station_id | linkedin | fb   | email |
+------------+----------+------+-------+
| 1          |        3 |    2 |     5 |
| 2          |        2 |    0 |     1 |
+------------+----------+------+-------+

You may also want to put an index on there to speed it up

ALTER TABLE posts ADD INDEX (station_id, type);

Explain output:

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | posts | index | NULL          | station_id | 28      | NULL |   13 | Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+