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;
SELECT * FROM newtable;
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;
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.