to count the absents from the table

ajoo · February 3, 2020

hi all !

I have the following set of data

Quote

+---------+-----------------------------+------------+
| RecNo | TimeOfLogout | Absents |
+---------+-----------------------------+------------+
| 30 | 2019-10-24 17:37:35 |    |
| | |       2   |
| 29 | 2019-10-21 15:23:54 | |
| | |       1 |
| 28 | 2019-10-19 12:31:23 | |
| | |       0 |
| 27 | 2019-10-18 16:37:43 | |
| | |       2 |
| 26 | 2019-10-15 16:56:55 | |
| |   |       9 |
| 25 | 2019-10-05 11:18:18 | |
| |   |       0 |
| 24 | 2019-10-04 21:41:17 | |
| | |       0 |
| 23 | 2019-10-03 13:28:17 | |
| | |       0 |
| 21 | 2019-10-02 07:47:35 | |
| | |       1 |
| 4 | 2019-09-30 13:13:15 | |
+---------+-----------------------------+------------+
| | |       15   |
+---------+-----------------------------+------------+

The first 2 columns are the data, the 3rd column, absent, is what I wish to calculate at each level of entry and finally then sum of all the absents till that particular entry. So here for example on RecNo 30, the total of all absents so far is 15. I wish to do this using mysql alone.

Any help appreciated.

Thanks !

Psycho · February 3, 2020

So, the 'absents' for a particular record (in your example 30) is the sum of all absents where the RecNo is less than or equal to 30?

SELECT SUM(Absents)
FROM [table_name]
WHERE RecNo <= 30

That will give you the total Absents for a single entry. But, if you want a query to return multiple records showing the Absents " . . . at each level of entry", I think the only solution is a sub-query - which would be very inefficient in this case (if ONLY doing in SQL). I would highly suggest querying for all the relevant records and calculating the Absents at each level in code.

ajoo · February 3, 2020

Hi Psycho,

The absents is not part of the table. It was there to show what I wanted as output. So all absents have to be calculated on the fly and then summed to get to the figure of 15 absents on date 30th.

Some kind of iterative loop is needed I guess.

Thanks.

Barand · February 3, 2020

This will calculate the "absents" value between consecutive logouts

SELECT recno
     , DATEDIFF(@prevlog, timeoflogout) - 1 as absents
     , @prevlog := timeoflogout as timeoflogout
FROM (
       SELECT recno
            , timeoflogout
       FROM ajoo
       ORDER BY timeoflogout DESC
     ) as sorted
     JOIN (SELECT @prevlog:=NULL) as initialise

+-------+---------+---------------------+
| recno | absents | timeoflogout        |
+-------+---------+---------------------+
| 30    |         | 2019-10-24 17:37:35 |
| 29    | 2       | 2019-10-21 15:23:54 |
| 28    | 1       | 2019-10-19 12:31:23 |
| 27    | 0       | 2019-10-18 16:37:43 |
| 26    | 2       | 2019-10-15 16:56:55 |
| 25    | 9       | 2019-10-05 11:18:18 |
| 24    | 0       | 2019-10-04 21:41:17 |
| 23    | 0       | 2019-10-03 13:28:17 |
| 21    | 0       | 2019-10-02 07:47:35 |
| 4     | 1       | 2019-09-30 13:13:15 |
+-------+---------+---------------------+

You can accumulate the total of "15" as you process the query results.

ajoo · February 3, 2020

Thank you Guru Barand !

It will take me some more time to decode your code but it works great !!

Thanks loads !

ajoo · February 3, 2020

Any way that we can count and get the total absents in the query itself.

I tried as below

SELECT recno
, DATEDIFF(@prevlog, TimeOfLogout) - 1 as absents
, @prevlog := TimeOfLogout as TimeOfLogout
, @tot := @tot+ absents as total
FROM (
SELECT recno
    , TimeOfLogout
FROM india_sessdata WHERE StudentLogin = 'nina12345'
ORDER BY TimeOfLogout DESC
) as sorted,
(SELECT @tot:=0) t
JOIN (SELECT @prevlog:=NULL) as initialise;

but it gives this error below 😒

Quote

Unknown column 'absents' in 'field list'

Thanks

Barand · February 3, 2020

"absents" is a column alias. You can't reference an alias inside the SELECT or WHERE parts of the same query.

If you want the cumulative then you'll need an extra subquery

SELECT recno
     , timeoflogout
     , absents
     , @tot := @tot + IFNULL(absents,0) as total
FROM (
        SELECT recno
             , DATEDIFF(@prevlog, timeoflogout) - 1 as absents
             , @prevlog := timeoflogout as timeoflogout
        FROM (
               SELECT recno
                    , timeoflogout
               FROM ajoo
               ORDER BY timeoflogout DESC
             ) as sorted
             JOIN (SELECT @prevlog := NULL, @tot := 0) as initialise
      ) recs;        
+-------+---------------------+---------+-------+
| recno | timeoflogout        | absents | total |
+-------+---------------------+---------+-------+
|    30 | 2019-10-24 17:37:35 |    NULL |     0 |
|    29 | 2019-10-21 15:23:54 |       2 |     2 |
|    28 | 2019-10-19 12:31:23 |       1 |     3 |
|    27 | 2019-10-18 16:37:43 |       0 |     3 |
|    26 | 2019-10-15 16:56:55 |       2 |     5 |
|    25 | 2019-10-05 11:18:18 |       9 |    14 |
|    24 | 2019-10-04 21:41:17 |       0 |    14 |
|    23 | 2019-10-03 13:28:17 |       0 |    14 |
|    21 | 2019-10-02 07:47:35 |       0 |    14 |
|     4 | 2019-09-30 13:13:15 |       1 |    15 |
+-------+---------------------+---------+-------+

"absents" is now a column in the subquery (a dynamic temporary table)

Edited February 3, 2020 by Barand

ajoo · February 3, 2020

Thank you !🙏

ajoo · February 4, 2020

Hi Guru Barand,

I modified your code just a trifle to get the table in the inverse order, and that makes it more accurate as a row has a complete information now for the query that I wish to execute.

Here's the code modified just so as also some var names,

SELECT recno
, timeoflogout
, gaps
, @tot := @tot + IFNULL(gaps,0) as absents
FROM (
SELECT recno
 , DATEDIFF(timeoflogout, @prevlog ) - 1 as gaps
 , @prevlog := timeoflogout as timeoflogout
FROM (
   SELECT recno
		, timeoflogout
   FROM ajoo 
   ORDER BY timeoflogout ASC
 ) as sorted
 JOIN (SELECT @prevlog := NULL, @tot := 0) as initialise
)recs;

which gives the output as

Quote

+-------+-----------------------------+--------+-----------+
| recno | timeoflogout | gaps | absents |
+-------+-----------------------------+--------+-----------+
| 4 | 2019-09-30 13:13:15 | NULL | 0 |
| 21 | 2019-10-02 07:47:35 | 1 | 1 |
| 23 | 2019-10-03 13:28:17 | 0 | 1 |
| 24 | 2019-10-04 21:41:17 | 0 | 1 |
| 25 | 2019-10-05 11:18:18 | 0 | 1 |
| 26 | 2019-10-15 16:56:55 | 9 | 10 |
| 27 | 2019-10-18 16:37:43 | 2 | 12 |
| 28 | 2019-10-19 12:31:23 | 0 | 12 |
| 29 | 2019-10-21 15:23:54 | 1 | 13 |
| 30 | 2019-10-24 17:37:35 | 2 | 15 |

The row that I now wish to examine is the last row of the table. It seems quite tricky to me to retrieve this value as if I invert the order to pick the first row, the calculated field remains at the last, unchanged, and If i use the recno reference at the end of the query (WHERE recno = 30), the calculated field becomes 0.

So How do I retrieve the last row values from the above table, if possible, without creating a temporary table and then querying it?

Thanks loads !

Barand · February 4, 2020

If what you really want is just the content of the latest row, then

SELECT 
       MAX(timeoflogout) as timeoflogout
     , SUM(absents)) as tot
FROM (
        SELECT recno
             , DATEDIFF(timeoflogout, @prevlog) - 1 as absents
             , @prevlog := timeoflogout as timeoflogout
        FROM (
               SELECT recno
                    , timeoflogout
               FROM ajoo
               ORDER BY timeoflogout ASC
             ) as sorted
             JOIN (SELECT @prevlog := NULL) as initialise
      ) recs;        
        
+---------------------+------+
| timeoflogout        | tot  |
+---------------------+------+
| 2019-10-24 17:37:35 |   15 |
+---------------------+------+

EDIT: P.S. Just curious - what is your next query that requires this data?

Edited February 4, 2020 by Barand

ajoo · February 5, 2020

Hi Guru Barand,

Thanks loads for your help,

Sir, so far, there is no further query that requires this data. It will be displayed on the home page of the tutor, so that he can see instantly the 'gap' ( the no of days elapsed between the current and last login) in logins of the students as well as 'absents', the totals number of days the student has been irregular in the program. He can then use this information to check this rate and minimize it or take suitable action.

For this I am actually using a complete separate table. I am doing it wrong as.I am storing the latest values of login and other parameters like lastlogin date, scores, calculated absents etc for each student in table A by taking them off from another table B that stores the same information for each day since the child registers. Then I am using this table A with latest values to display the status, lastlogin date and scores and gaps and absents etc after due calculation in php on the tutor's home page. Creating table A from B is incorrect since its duplication of data.

With this query, I will to remove table A completely and do the calculations on the fly of gaps and absents and scores ( which were on the fly in any case but I was storing them in table A) to display them on the tutor home page.

Thanks loads !🙏

Barand · February 5, 2020

The method of counting the dates not present between the logout dates has a major flaw - it does not take into account those days when they should not be present (ie weekends and holidays) and just counts them as absent on those days.

The method I usually use in these situations is to create a temporary table (workdays) which contains all the weekday dates for the required period.

In the logout data I have also the data for several users during this period (Oct 1 to Oct 24 2019).

CODE TO GENERATE workday DATES

$startdate = '2019-10-01';
$enddate   = '2019-10-25';
$interval = DateInterval::createFromDateString('next weekday');
$dateperiod = new DatePeriod( new DateTime($startdate), $interval, new DateTime($enddate));

$db->exec("CREATE TEMPORARY TABLE workday (day DATE not null primary key)");
foreach ($dateperiod as $d) {
    $data[] = "('{$d->format('Y-m-d')}')";
}
$db->exec("INSERT INTO workday VALUES " . join(',', $data));

I also have a "holiday" table. In this example, Oct 14th is designated a holiday.

So the data I now have is...

TABLE: ajoo_log                                 TABLE: ajoo_holiday         
+-------+---------------------+--------+        +------------+------------+-------------+         
| recno | timeoflogout        | userid |        | holiday_id | hol_date   | hol_name    |         
+-------+---------------------+--------+        +------------+------------+-------------+         
| 3     | 2019-09-30 13:13:15 | 3      |        | 1          | 2019-10-14 | October Day |         
| 4     | 2019-09-30 13:13:15 | 4      |        +------------+------------+-------------+         
| 5     | 2019-09-30 13:13:15 | 5      |         
| 9     | 2019-10-01 07:47:35 | 4      |        TABLE: ajoo_user      TEMP TABLE: workday
| 10    | 2019-10-01 07:47:35 | 5      |        +--------+-------+         +------------+
| 14    | 2019-10-03 13:28:17 | 4      |        | userid | name  |         | day        |
| 15    | 2019-10-03 13:28:17 | 5      |        +--------+-------+         +------------+
| 18    | 2019-10-04 21:41:17 | 3      |        | 3      | Curly |         | 2019-10-01 |  Tue
| 19    | 2019-10-04 21:41:17 | 4      |        | 4      | Larry |         | 2019-10-02 |
| 20    | 2019-10-04 21:41:17 | 5      |        | 5      | Mo    |         | 2019-10-03 |
| 23    | 2019-10-05 11:18:18 | 3      |        +--------+-------+         | 2019-10-04 |
| 24    | 2019-10-05 11:18:18 | 4      |                                   | 2019-10-07 |  Mon
| 25    | 2019-10-05 11:18:18 | 5      |                                   | 2019-10-08 |
| 28    | 2019-10-08 16:56:55 | 3      |                                   | 2019-10-09 |
| 29    | 2019-10-10 16:56:55 | 4      |                                   | 2019-10-10 |
| 30    | 2019-10-16 16:56:55 | 5      |                                   | 2019-10-11 |
| 33    | 2019-10-18 16:37:43 | 3      |                                   | 2019-10-14 |  Mon
| 34    | 2019-10-18 16:37:43 | 4      |                                   | 2019-10-15 |
| 35    | 2019-10-18 16:37:43 | 5      |                                   | 2019-10-16 |
| 38    | 2019-10-19 12:31:23 | 3      |                                   | 2019-10-17 |
| 40    | 2019-10-19 12:31:23 | 5      |                                   | 2019-10-18 |
| 43    | 2019-10-21 15:23:54 | 3      |                                   | 2019-10-21 |  Mon
| 44    | 2019-10-21 15:23:54 | 4      |                                   | 2019-10-22 |
| 45    | 2019-10-21 15:23:54 | 5      |                                   | 2019-10-23 |
| 48    | 2019-10-24 15:37:35 | 3      |                                   | 2019-10-24 |
| 49    | 2019-10-24 16:37:35 | 4      |                                   +------------+
| 50    | 2019-10-24 17:37:35 | 5      |
+-------+---------------------+--------+

Now to calculate the days absent.

A cross join of the user table with workday table gives me a date for each user when they were expected to attend.

Left joining to the log table and the holiday tables tells me when they were absent (no matching logout and no matching holiday)

SELECT u.name
     , SUM(ISNULL(timeoflogout) AND ISNULL(hol_date)) as total_absent
FROM ajoo_user u 
       CROSS JOIN
     workday w
       LEFT JOIN 
     ajoo_log l ON u.userid = l.userid
                AND w.day = DATE(l.timeoflogout)
       LEFT JOIN
     ajoo_holiday h ON w.day = h.hol_date
GROUP BY u.userid

Comparison of original method with this new method

ORIGINAL RESULTS                           NEW RESULTS
+----------+--------------+                +-------+--------------+
| username | total_absent |                | name  | total_absent |
+----------+--------------+                +-------+--------------+
| Curly    | 14           |                | Curly | 12           |
| Larry    | 16           |                | Larry | 10           |
| Mo       | 15           |                | Mo    | 10           |
+----------+--------------+                +-------+--------------+

Edited February 5, 2020 by Barand

ajoo · February 16, 2020

Hi Guru Barand,

Just saw this. Thanks for the insight into the handling of holidays and pointing out the flaw.

Thank you so much,

Truly grateful !

Sign In

to count the absents from the table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information