...

Inter-row Calculations – Get Remaining Course Days by Month

 
SQL(179)

【Question】

I want to list data grouped   by month, and the days remaining to complete the course in the next column.   The Course has 10 days.

Example data:

ID  Name    Date

1  Sandy    2015-05-06

2  Candy    2015-05-06

3  Sandy    2015-05-28

4  Candy    2015-05-29

5  Candy    2015-06-01

 

Preferred output：

|Name|Month|Attended|Remaining|

|Sandy|May|2|8|

|Candy|May|2|8|

|Candy|June|1|7|

 

I try to use GROUP BY DATE_FORMAT(date,    '%Y%m'), Name to do the calculation,   but it does not work.

Correct solution:

Two SQLs:

SELECT Name, YEAR(DATE) AS YY,MONTH(DATE) AS MM, COUNT(*) AS Attended

 

FROM test

GROUP BY Name, YEAR(DATE),   MONTH(DATE)

SET @currcount = NULL,   @currvalue = NULL;

SELECT Name

, YY

, MM

, Attended

, @currcount  := IF(@currvalue = Name, @currcount, 10) -   Attended AS Remaining

, @currvalue  := Name AS dontcare

FROM (

SELECT Name,  YEAR(DATE) AS YY, MONTH(DATE) AS MM,   COUNT(*) AS Attended

FROM test

GROUP BY Name,  YEAR(DATE), MONTH(DATE)

) AS whatever

ORDER BY Name, YY, MM

 

【Answer】

Like the above solution, you need a variable to do this in MySQL. It’s intuitive to handle the post-grouping inter-row calculations in SPL (Structured Process Language):

A

1

$select * from tb

2

=A1.groups(Name,left(string(Date),7):Month;count():Attended)

3

=A2.derive(if(Name==Name[-1],Remaining[-1],10)-Attended:Remaining)

A1:  Retrieve data in SQL.

A2: Group data by Name and Month and count the attended days in the current month for each person.

A3: Add Remaining column to record the remaining days in the current month for each person.


SQL(179)
 
 •  392 View  •