In Progress

Correct or provide a correct SQL statement for grouping data from single table.

I have a table that has 5 fields.

MyTable (ts int, pid int, pfc int, psc int, spt int)

The table stores unique records. I mean no record will present in the table twice.

The stored data essentially is grouped by the "ts" field.

I mean all the records that have the same "ts" value can be considered a single group.

The task is to write a SQL statement in such a way that we find the frequency of the records with respect to a single group and with respect to the entire groups.

For example:

If we have this records:

INSERT INTO MyTable VALUES (10, 1, 0, 0, 0) ;

INSERT INTO MyTable VALUES (10, 1, 0, 0, 0) ;

INSERT INTO MyTable VALUES (10, 1, 0, 0, 1) ;

INSERT INTO MyTable VALUES (10, 1, 0, 0, 1) ;

INSERT INTO MyTable VALUES (10, 1, 1, 0, 1) ;

INSERT INTO MyTable VALUES (11, 1, 0, 0, 1) ;

INSERT INTO MyTable VALUES (11, 1, 0, 0, 1) ;

INSERT INTO MyTable VALUES (11, 1, 0, 0, 1) ;

INSERT INTO MyTable VALUES (11, 1, 0, 2, 0) ;

INSERT INTO MyTable VALUES (11, 1, 0, 2, 0) ;

The final result of the desired SQL statement is

ts | pid | pfc | psc | spt | thiscount | accmulatedcount

10 | 1 | 0 | 0 | 0 | 2 | 2

10 | 1 | 0 | 0 | 1 | 2 | 2

11 | 1 | 0 | 0 | 1 | 3 | 5 <-- because we have 2 in the 10 group

11 | 1 | 0 | 2 | 0 | 2 | 2

11 | 1 | 1 | 0 | 1 | 0 | 1 <-- NOTICE: thiscount =0 because no reletive record in the group 11 HOEVER the "accmulatedcount" reveals the "1" from the previous records in any. If we have more than groups then we should do the same with them.

For a clear example please see the attached image.

Skills: PostgreSQL, SQL, SQLite

See more: mysql, database programming, postgresql

Project ID: #17525236