Help for grouping a field and taking total sum

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.

• I am using mysql 14.4 May 23, 2018 at 10:39
• 14.4? Can you do a: `select @@VERSION` and post the result May 23, 2018 at 10:44
• 5.7.22-0ubuntu18.04.1 Thanks for replying me May 23, 2018 at 12:11

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
``````

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      |
``````
• thanks for quick reply, I tried but in mysql over() is not available. May 23, 2018 at 10:44
• @RamaKrishnaMajety, I have added the mysql tag to your question. Always tag questions with your DBMS if you want a solution for a particular one May 23, 2018 at 11:32

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;
``````
``````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