CODEWITHSUNDEEP
mysql
kt_at_kt
kt_at_kt

Reputation: 343

MySql query accumlate count for each record before the time point of the record in the same table

I have a table to store student attendance with a date time, I would like to find out for each record the accumulative attendance count before the time at that record.

Below is the original table:

+++++++++++++++++++++++++++++++++++++
|id| sid |  IsAttend  |  DateTime   |
-------------------------------------
| 1|  1  |     1      |  2020-01-01 |
| 2|  2  |     0      |  2020-01-01 |
| 3|  1  |     1      |  2019-12-31 |
| 4|  2  |     1      |  2019-12-31 |
| 5|  3  |     1      |  2019-12-31 |
| 6|  1  |     0      |  2019-12-25 |
| 7|  3  |     1      |  2019-12-25 |
| 8|  1  |     1      |  2019-12-20 |
+++++++++++++++++++++++++++++++++++++

And below is what I want:

+++++++++++++++++++++++++++++++++++++++++++++++
|id| sid |  IsAttend  |  DateTime   | a_count |
-----------------------------------------------
| 1|  1  |     1      |  2020-01-01 |    2    |
| 2|  2  |     0      |  2020-01-01 |    1    |
| 3|  1  |     1      |  2019-12-31 |    1    |
| 4|  2  |     1      |  2019-12-31 |    0    |
| 5|  3  |     1      |  2019-12-31 |    1    |
| 6|  1  |     0      |  2019-12-25 |    1    |
| 7|  3  |     1      |  2019-12-25 |    0    |
| 8|  1  |     1      |  2019-12-20 |    0    |
+++++++++++++++++++++++++++++++++++++++++++++++

PS. For the first record, since sid 1 has 2 IsAttend before the first record, so it is 2 in a_count

How to use MySql to query this?

Thanks

Upvotes: 0

Views: 62

Answers (2)

FanoFN
FanoFN

Reputation: 7114

I'm assuming the example output was including of some other data. In this case you can do a LEFT JOIN to itself and perform COUNT(*) afterwards. Refer following example:

SELECT    a.id, a.sid, a.isattend, max(a.date),COUNT(b.date) 
FROM      mytable a 
LEFT JOIN mytable b 
ON        a.sid=b.sid 
AND       a.isattend=b.isattend 
AND       a.date > b.date 
GROUP BY  a.id, a.sid, a.isattend;

Edit:

SELECT a.id, a.sid, a.isattend, max(a.date),ifnull(sum(b.isattend),0)
FROM mytable a 
LEFT JOIN mytable b 
ON a.sid=b.sid 
AND a.date > b.date GROUP BY a.id, a.sid, a.isattend;

Fiddle here: https://www.db-fiddle.com/f/9kKmyHsSAMNk4BBPDWLHFh/5

Upvotes: 1

Sahan Dissanayaka
Sahan Dissanayaka

Reputation: 621

So in your case you want that the count for the relevant sid id

if you want it in a different view query has to be select sid from tablename count sid

then you will get reletive count for id

Upvotes: 0

Related Questions