Geek Answers Handbook

How to group using sql subqueries

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

station 1 has 3 fb messages, 6 related messages, 5 email messages station 2 has 3 fb messages, 6 related messages, 5 email messages

So I need to group by station ID, my table structure

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 that I have so far returns station 0 as having 2 attached messages when it has one (2 in 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;
+4
source share
4 answers

Or, the fastest way, avoiding joins and subqueries, to get it in the right format:

 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 can also specify an index to speed it up.

 ALTER TABLE posts ADD INDEX (station_id, type);

Explain the conclusion:

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

As implied by the gnif answer, having three correlated sub_queries has overhead performance. Depending on the DBMS used, it can be performed in the same way as it is combined three times.

The gnif methodology ensures that a table is parsed only once, without the need for concatenation, correlated sub_queries, etc.

The immediately obvious side to gnif's answer is that you never get entries for 0. If there are no fb types, you just don't get the entry. If this is not a problem, I would go with his answer. If this is a problem, however, here is a version with a similar methodology for gnif, but matching your output format ...

 SELECT station_id, SUM(CASE WHEN type = 'linkedin' THEN 1 ELSE 0 END) AS linkedin_count, SUM(CASE WHEN type = 'fb' THEN 1 ELSE 0 END) AS fb_count, SUM(CASE WHEN type = 'email' THEN 1 ELSE 0 END) AS email_count FROM posts GROUP BY station_id
+2
source

Give this move:

 SELECT station_id, type, count(*) FROM posts GROUP BY station_id, type

The output format will be slightly different from what you are trying to get, but it should provide the statistics you are trying to get. Also, since its only request is much faster.

- Change, add a sample result set

 +------------+----------+----------+ | station_id | type | count(*) | +------------+----------+----------+ | 1 | fb | 2 | | 1 | linkedin | 3 | | 1 | email | 5 | | 2 | linkedin | 2 | | 2 | email | 1 | +------------+----------+----------+
+1
source

try the following:

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

More articles:


All Articles