Mysql – Help for grouping a field and taking total sum

countgroup byMySQLsum

I have a sorted data in a table 

F1  F2  F3  F4
A   M   X   1
A   N   Y   2
A   P   X   3
B   M   X   3
B   N   Y   1
B   P   X   5

but I want the sub count of values in F1 column for every row. like this 

F1  F2  F3  F4  Sub Total
A   M   X   1   6
A   N   Y   2   6
A   P   X   3   6
B   M   X   3   9
B   N   Y   1   9
B   P   X   5   9

I tried to take sum

select *, sum(count) from table group by F1, F2, F3;
But this is not giving total in the last column.

Please help me

Best Answer

One way is to do a sub-select for the sum of F4 (this is the only explanation I could come up with in order to get 6 and 9)

SELECT x.F1,  x.F2,  x.F3,  x.F4
     , ( SELECT SUM(F4)
         FROM T as y
         WHERE x.F1 = y.F1 ) as sub_total
FROM T as x

If you DBMS support window functions:

SELECT x.F1,  x.F2,  x.F3,  x.F4
     , SUM(x.F4) OVER (PARTITION BY x.F1) as sub_total
FROM T as x
Related Question