Sum of several subqueries

Question

I'm trying to sort a table (let's call it Parent) by the number of relations it has to two tables let's call them Child1 and Child2). Right now I'm doing it like this:

SELECT Master.id
 (SELECT COUNT(*) FROM Child1 WHERE Child1.id=Master.id) 
 + (SELECT COUNT(*) FROM Child2 WHERE Child2.id=Master.id) AS  score
 ORDER BY score DESC;

It works as expected, but is difficult to maintain as I have no way of knowing what the intermediate values of the subqueries are. What I'd like to is the following:

 SELECT Master.id
 (SELECT COUNT(*) FROM Child1 WHERE Child1.id=Master.id) AS child1_score,
 (SELECT COUNT(*) FROM Child2 WHERE Child2.id=Master.id) AS  child2_score,
 child1_score + child2_score AS score
 ORDER BY score DESC;

But that gives me an error saying child1_score and child2_score aren't defined. The only clean way I can think of doing this is with an additional level of nested subqueries:

SELECT child1_score + child2_score AS score, id FROM
    (SELECT Master.id
     (SELECT COUNT(*) FROM Child1 WHERE Child1.id=Master.id) AS child1_score,
     (SELECT COUNT(*) FROM Child2 WHERE Child2.id=Master.id) AS  child2_score)
    ORDER BY score DESC;

But that seems like it would be far worse for memory.

Is there a better way to deal with this that I'm not seeing? I'm using MySQL but I'd like to stick to standard syntax if possible.

Your solution seems fine to me – Strawberry Sep 28 '14 at 07:04 — Strawberry, Sep 28 '14 at 07:04

ElmoVanKielmo · Answer 1 · 2013-07-09T08:27:30.113

Well, it's not worse for memory - remember that DBMS (in particular MySQL) has a planner and optimizes your query on the fly. You should avoid Cartesian Products in your queries but there really is no significant difference between your last query and proposed by @KaeL. The intermediate aliases child1_score and child2_score are internally just pointers so please don't bother.
In the end, if we couldn't rely on DBMS reliability and performance, why would we use DBMS?

EDIT: For examining how exactly your query is going to be executed you need EXPLAIN http://dev.mysql.com/doc/refman/5.0/en/explain.html
There seems to be no way to preserve intermediate aliases from sub-queries without explicitly selecting them in top level query. Testing if sub-queries work as expected has to be done solely and then optimization by constructing nested query and further by profiling with EXPLAIN takes place. This approach works fine in my projects.

I think you misunderstand my question. I want to preserve the internal aliases. In this example, I'm doing a simple sum for the sake of keeping the question short. The query I'm actually working on uses a more complicated formula, and I need to see the values of each sub-query to reliably judge how the query is doing. — Lee Avital, Jul 09 '13 at 07:49

score 1 · Answer 2 · edited May 23 '17 at 12:24

It's too bad that you're using MySQL. If you were using just about any other DBMS, you could use the WITH clause. Then your query would look something like this:

WITH First AS (
    SELECT id, COUNT(*) AS Cnt FROM Child1 GROUP BY id
),
Second AS (
    SELECT id, COUNT(*) AS Cnt FROM Child2 GROUP BY id
)
SELECT Master.id, First.Cnt, Second.Cnt, First.Cnt+Second.Cnt as score
    FROM Master
    INNER JOIN First ON Master.id=First.id
    INNER JOIN Second ON Master.id=Second.id
    ORDER BY score DESC;

Using this method you can add as many intermediate subqueries as you like, and you can refer to columns from earlier subqueries too (not just in the last one). Unfortunately I'm not sure if there is a way to accomplish what you are trying to do using MySQL alone.

score 0 · Answer 3 · answered Jul 09 '13 at 07:24

Based from the last query you have:

SELECT child1_score + child2_score AS score, id FROM
    (SELECT Master.id
     (SELECT COUNT(*) FROM Child1 WHERE Child1.id=Master.id) AS child1_score,
     (SELECT COUNT(*) FROM Child2 WHERE Child2.id=Master.id) AS  child2_score)
    ORDER BY score DESC;

Makes me think that you only need Master.id and score to be SELECTed.

I guess this query should be fine:

SELECT Master.id
(SELECT COUNT(*) FROM Child1 WHERE Child1.id=Master.id) + (SELECT COUNT(*) FROM Child2 WHERE Child2.id=Master.id) AS score
FROM Master
ORDER BY score DESC;

That's the first query I wrote indented differently. This works fine for now, but as I said, is difficult to maintain as I don't have a means of seeing the intermediate values of the subqueries. — Lee Avital, Jul 09 '13 at 07:44

Sum of several subqueries

3 Answers3