MYSQL subquery results columns not as expected

I am trying to build a query that will build a output like this.

 +-----------------+--------------+
 | Name            | patient appt |
 +-----------------+--------------+
 | Dharmick Ketan  |           75 |
 | See Ka Wai      |           45 |
 | Totoritis Susan |           25 |
 | Seay Duval      |          147 |
 +-----------------+--------------+

The output that I go is this

 +-----------------+--------------+
 | Name            | patient appt |
 +-----------------+--------------+
 | Dharmick Ketan  | patient appt |
 | See Ka Wai      | patient appt |
 | Totoritis Susan | patient appt |
 | Seay Duval      | patient appt |
 +-----------------+--------------+

I was following the instructions here https://www.mysqltutorial.org/mysql-subquery/

My query is this

 mysql> select concat(p.lname,' ' , p.fname) as 'Name',
->                         'patient appt'   
->                         from patient_data as p
->                         where
->                          p.financial_review_exp>'2019-07-01'
->                         and
->                         'patient appt' = ( select count(*) from openemr_postcalendar_events e
->                         where e.pc_pid = p.pid );

What I was expecting to happen was the alias ‘patient appt’ to be populated with the data from the nested select statement. I was thinking this would work because I am trying to produce multiple columns that are going to be populated by subqueries. All of the columns are counts of appointments in the calendar table.

So, does the ‘patient appt’ need to be a column in the patient_data table? If so, is there another way to produce the desired column data?

Answer

Put the correlated subquery directly in the select clause:

select concat(p.lname,' ' , p.fname) as name,
    (select count(*) from openemr_postcalendar_events as e where e.pc_pid = p.pid) as patient_appt
from patient_data as p
where p.financial_review_exp > '2019-07-01'

Note: don’t use single quotes for column aliases! They are meant for literal strings only. In MySQL, you can use backticks to quote identifiers – but better yet, use identifiers that do not require quoting, so you don’t have to worry about that.