Count with a subselect yielding double the amount

Question

I'm new to SQL.

Problem: Say if I were to count the amount that is contained in the alias table of "x" COUNT(x.xValue) to be 217. Now when I add the sub-query "y" and then do the count again, I have the COUNT(x.xValue) to suddenly square its self -> 47089. Why is this happening?

(Note: Both alias tables "x" and "y" have the same amount -> 217.)

How do I fix this problem. I don't want to use Variables or Views.

SELECT COUNT(x.xValue) + COUNT(y.yValue) AS CountXY
FROM 
(SELECT value AS xValue FROM table1
            WHERE 
            ...) AS x,
(SELECT value AS yValue FROM table1 
            WHERE 
            ...) AS y

Result of 'CountXY' : 94178.

Result I'm expecting 'CountXY' : 434

What you wrote there is called a theta-join or "the old way of doing joins". Every row of table `x` will be joined to every row of table `y`. If both tables contain 217 rows, you'll get what you said `x * y`. Since x and y in your case are both 217, you get the square of 217. What you actually want is `SELECT (SELECT COUNT(*) FROM table1 WHERE..) + (SELECT COUNT(*) FROM table2 WHERE..) AS my_total_count)`. — N.B., Mar 09 '15 at 21:57

score 0 · Accepted Answer · answered Mar 10 '15 at 02:34

The problem is that you are doing two sub-queries and then trying to call the values return directly.

This will behave as selecting one value from table x and matching it to every single value in table y. This obviously creates the squared return effect.

What you need to use is the JOIN to combine both data-sets so that you get the 1 to 1 relationship you are trying to achieve.

This is how the above should be done with your previous sub-query:

SELECT COUNT(A.value) AS x, COUNT(B.value) AS y
FROM table1 AS A
    JOIN table1 AS B
        ON A.attr1 = B.attr1
        AND A.attr2 = B.attr2
    WHERE B.attr1 != 'whatever'
    AND B.attr2 = 'whatever'
    AND A.attr3 = 'something'
    AND B.attr3 = 'something different'

The above query should return the correct 1 to 1 relationship you are looking for. Replacing your sub-query with the one above should give you the correct answer

Count with a subselect yielding double the amount

1 Answers1