Find number of times employee attended meeting from Table Limit

Question

I have a table titled meetings managing the number of times an employee has been marked present for attending meetings for a range of dates. Table is just e_id and Date. If I have 50 Dates 2021-09-30, 2021-09-29... and an employee has been marked present 7 times in the table during the last 10 scheduled meetings, I am trying to show how many times the employee has been present for the last 10 meetings to determine a trend. For example, my query would look up the employee ID and display 7 for the example above. This is not working. I may have a typo. MYSQL states #1054 - Unknown column 'e_id' in 'field list'. Any help would be appreciated.

SELECT COUNT(e_id) AS 'Present'
FROM (SELECT DISTINCT Date FROM meetings ORDER BY Date DESC LIMIT 10) a
WHERE e_id = '".$sidw."'

Answer 1

The subquery that you use to get the last 10 dates does not return the column e_id and this is why you get the unknown column error.

If your version of MySql is 8.0+ you can use DENSE_RANK() window function to get the rows of the last 10 dates and then aggregate:

SELECT COUNT(*) AS Present
FROM (
  SELECT *, DENSE_RANK() OVER (ORDER BY date DESC) dr 
  FROM meetings 
) t
WHERE e_id = '".$sidw."' AND dr <= 10;

For previous versions use a join to the distinct dates:

SELECT COUNT(*) AS Present
FROM (SELECT DISTINCT date FROM meetings ORDER BY Date DESC LIMIT 10) d
INNER JOIN meetings m ON m.date = d.date
WHERE m.e_id = '".$sidw."';