NitrusAphalion NitrusAphalion - 1 year ago 38
MySQL Question

Why does accessing COUNT field from subquery return only one result?

Am trying to wrap my head around why when I access this table the these two ways I get two different results:

Query #1: Returns a single result (I am trying to get all rows with the additional SUM column)

SELECT *, SUM(matches) FROM newtable;

Query #2: Returns all results as expected

SELECT * FROM newtable;

Table definition:

CREATE TABLE IF NOT EXISTS newtable (pattern TEXT, matches INT);

INSERT INTO newtable
SELECT pattern, COUNT(*) AS matches
FROM (SELECT pattern FROM cl_ra_30 WHERE id IN
(SELECT CASE WHEN id = id THEN id + 1 END AS id FROM cl_ra_30 WHERE pattern = '2_1_4_True')) AS K
GROUP BY pattern HAVING matches > 1 ORDER BY matches DESC;

Answer Source

This is your query (with a table alias and qualified column names):

select t.*, sum(t.matches)
from newtable t;

Because of the sum() function, this is an aggregation query. Often an aggregation query has a group by clause, specifying the groups for aggregation. In this case, there is no group by, so SQL specifies that exactly one row is returned, where all rows are in the same group.

In most databases, this query would return an error. MySQL allows this construct, choosing values for the non-aggregated columns from indeterminate rows.

Probably the best way to get what you want uses a subquery:

select t.*, tt.summatches
from newtable t cross join
     (select sum(t.matches) as summatches from newtable t) tt;

In other databases, you would simply use window functions:

SELECT t.*, SUM(t.matches) OVER ()
FROM newtable t;

But MySQL does not (yet) support window functions.