Turning The Tables - MySQL

You can also use the results generated by a subquery as a table in the FROM clause of an enclosing SELECT statement. In order to illustrate, let's say I want to know the average number of services each branch office has. Therefore, the first thing I need to do is group the branches together and count the total number of services each one has,

mysql> SELECT bid, COUNT(sid) AS stotal FROM branches_services GROUP BY 

mysql> bid;

+------+--------+

| bid | stotal |

+------+--------+

| 1011 | 4 |

| 1012 | 1 |

| 1013 | 1 |

| 1031 | 3 |

| 1032 | 1 |

| 1033 | 1 |

| 1041 | 2 |

| 1042 | 2 |

| 1101 | 1 |

+------+--------+

9 rows in set (0.17 sec)

and then calculate the rounded-off average of the per-branch totals. Or, in a subquery,

mysql> SELECT AVG(z.stotal) FROM (SELECT bid, COUNT(sid) AS stotal FROM

branches_services GROUP BY bid) AS z;

+---------------+

| AVG(z.stotal) |

+---------------+

| 1.7778 |

+---------------+

1 row in set (0.21 sec)

Thus, the table of results generated by the inner query is used in the FROM clause of the outer query. You can convert the average number above into an integer with the CEILING() function if you like.

Note that when using subquery results in this manner, the result table generated by the inner query must be first aliased to a table name, or else MySQL will now know how to refer to columns within it. Look what happens when I re-run the query above without the table alias:

mysql> SELECT AVG(stotal) FROM (SELECT bid, COUNT(bid) AS stotal FROM

branches_services GROUP BY bid);

ERROR 1246: Every derived table must have it's own alias 

Now, let's take it a step further. What if I need to list the branches where the number of services is above this average?

mysql> SELECT bid FROM branches_services GROUP BY bid HAVING COUNT(sid) 

mysql> >

1.7778;

+------+

| bid |

+------+

| 1011 |

| 1031 |

| 1041 |

| 1042 |

+------+

4 rows in set (0.28 sec)

To make it truly dynamic, you should combine the two queries above into the following complex query:

mysql> SELECT bid FROM branches_services GROUP BY bid HAVING COUNT(sid) 

mysql> >

(SELECT AVG(z.stotal) FROM (SELECT bid, COUNT(bid) AS stotal FROM branches_services 
GROUP BY bid) AS z);

+------+

| bid |

+------+

| 1011 |

| 1031 |

| 1041 |

| 1042 |

+------+

4 rows in set (0.28 sec)

Wanna make it really complicated? Add another query (and a join) around it to get the customer names corresponding to those branches.

mysql> SELECT DISTINCT cname FROM clients, branches, (SELECT bid FROM

branches_services GROUP BY bid HAVING COUNT(sid) > (SELECT AVG(z.stotal) 
FROM (SELECT bid, COUNT(bid) AS stotal FROM branches_services GROUP BY bid) 
AS z)) AS x WHERE clients.cid = branches.cid AND branches.bid = x.bid;

+------------------+

| cname |

+------------------+

| JV Real Estate |

| DMW Trading |

| Rabbit Foods Inc |

+------------------+

3 rows in set (0.33 sec)

Whew!