0

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

  • I am using mysql 14.4 – Rama Krishna Majety May 23 '18 at 10:39
  • 1
    14.4? Can you do a: select @@VERSION and post the result – Lennart May 23 '18 at 10:44
  • 5.7.22-0ubuntu18.04.1 Thanks for replying me – Rama Krishna Majety May 23 '18 at 12:11
2

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
1

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. – Rama Krishna Majety May 23 '18 at 10:44
  • 1
    @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 – Lennart May 23 '18 at 11:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.