How to query MYSQL table to get counts of column occurrence overall and within past year?

I am using MySQL and want to create a query that will count how many times appointments happened per contact_id within the last year, last year, and how many times the occurrence happened overall.

My appointment table has the following rows with sample data:

id | contact_id | appointment_date | status_id

1  |  1         |  2019-01-01      | Attended
2  |  1         |  2018-05-01      | Attended
3  |  2         |  2019-04-01      | Cancelled
4  |  2         |  2019-05-01      | No Show
5  |  1         |  2018-07-01      | Cancelled
6  |  1         |  2019-08-01      | Not Complete

So let’s say I want to see how many times contact_id ‘1’ had appointments, here how I’d want the result to look:

 Status       |  Total overall | Total this year | Total last year
 Attended     |  2             | 1               | 1
 Cancelled    |  1             | 0               | 1
 No Show      |  0             | 0               | 0
 Not Complete |  1             | 1               | 0

Is this query do-able with MYSQL?

I will be using it in an HTML/PHP/jQuery web application. I am going to have a button that will generate this information once clicked. Perhaps I should scrap the query idea and somehow manipulate it using some sort of jQuery table/data manipulation after a simple

‘SELECT * from appointments WHERE contact_id = 1'?

Answer

If you’d like to have it in a single query, you can try something like this:

SELECT
      status_id
    , COUNT(*) AS `Total overall`
    , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()), 1, 0)) AS `Total this year`
    , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()) - 1, 1, 0)) AS `Total last year`
FROM
    appointment
WHERE
    contact_id = 1
GROUP BY
    status_id

=>

status_id   total   total_this_year total_last_year
Attended    2   1   1
Cancelled   1   0   1
Not Complete    1   1   0

The above query will not return information for ‘No Show’. If you really need all statuses, you can do something like this:

SELECT
      s.status_id
    , IFNULL(a.total, 0) AS `Total overall`
    , IFNULL(a.total_this_year, 0) AS `Total this year`
    , IFNULL(a.total_last_year, 0) AS `Total last year`
FROM
    (
        SELECT
            DISTINCT status_id
        FROM
            appointment
    ) AS s
    LEFT JOIN (
        SELECT
              status_id
            , COUNT(*) AS `total`
            , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()), 1, 0)) AS `total_this_year`
            , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()) - 1, 1, 0)) AS `total_last_year`
        FROM
            appointment
        WHERE
            contact_id = 1
        GROUP BY
            status_id
    ) AS `a` ON (
        a.status_id = s.status_id
    )

=>

status_id   Total overall   Total this year Total last year
Attended    2   1   1
Cancelled   1   0   1
Not Complete    1   1   0
No Show 0   0   0

P.S. I do not like the years calculations I used in the query, however, keeping it as is here for simplicity. In real life, I’d use range comparison appointment_date BETWEEN CONCAT(YEAR(CURDATE()), '-01-01') AND CONCAT(YEAR(CURDATE()), '-12-31') instead of YEAR(appointment_date) = YEAR(CURDATE()).