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

am trying wrap head around why when access table these 2 ways 2 different results:

query #1: returns single result (i trying rows additional sum column)

select *, sum(matches) newtable; 

query #2: returns results expected

select * newtable; 

table definition:

create table if not exists newtable (pattern text, matches int);  insert newtable select pattern, count(*) matches (select pattern cl_ra_30 id in (select case when id = id id + 1 end id cl_ra_30 pattern = '2_1_4_true')) k group pattern having matches > 1 order matches desc; 

this query (with table alias , qualified column names):

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

because of sum() function, aggregation query. aggregation query has group by clause, specifying groups aggregation. in case, there no group by, sql specifies 1 row returned, rows in same group.

in databases, query return error. mysql allows construct, choosing values non-aggregated columns indeterminate rows.

probably best way want uses subquery:

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

in other databases, use window functions:

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

but mysql not (yet) support window functions.