Help for grouping a field and taking total sum

Question

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

Answer 1

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

Answer 2

You didn't specify your RDBMS, but here is a solution using SQL Server.

declare @T table (F1 char(1), F2 char(1), F3 char(3), F4 smallint)
insert into @T(F1,F2,F3,F4) VALUES
('A',   'M',   'X',   1),
('A',   'N',   'Y',   2),
('A',   'P',   'X',   3),
('B',   'M',   'X',   3),
('B',   'N',   'Y',   1),
('B',   'P',   'X',   5)

SELECT *,
SUM(F4) OVER(PARTITION BY F1 ORDER BY F1) AS SUMCNT
 FROM @T

---

| F1 | F2 | F3 | F4 | SUMCNT |
|----|----|----|----|--------|
| 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      |

Answer 3

Another versions of @Lennart's query with subselect:

SELECT x.F1,  x.F2,  x.F3,  x.F4, z.SF4
FROM T as x, ( SELECT y.F1, SUM(y.F4) as SF4
               FROM T as y
               GROUP BY y.F1 ) as z
WHERE x.F1 = z.F1;

or

SELECT x.F1,  x.F2,  x.F3,  x.F4, z.SF4
FROM T as x 
JOIN ( SELECT y.F1, SUM(y.F4) as SF4
       FROM T as y
       GROUP BY F1 ) as z
   ON x.F1 = z.F1;

Answer 4

create table tbl (F1 char(1), F2 char(1), F3 char(1), F4 int);
insert into tbl values
('A',   'M',   'X',   1),
('A',   'N',   'Y',   2),
('A',   'P',   'X',   3),
('B',   'M',   'X',   3),
('B',   'N',   'Y',   1),
('B',   'P',   'X',   5);
GO

6 rows affected

SELECT F1, F2, F3, F4,
       SUM(F4) OVER (PARTITION BY F1) AS SUB_TOTAL
FROM   tbl;
GO

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

db<>fiddle here