Find number of times employee attended meeting from Table Limit-CodePudding

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."'

CodePudding user response：

Your subquery syntax is wrong, for the result you want, you have to use the distinct SELECT as a WHERE/AND condition !

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

CodePudding user response：

The error you get is because the inner query expose only the Date field, and not the e_id. You can modify it this way:

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

You'll filter the e_id in the inner query, then do a count to whatever the inner query returns

CodePudding user response：

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."';