SQL query counting stats – counting if this is the first record in a table for a particular user

I’m building some basic stats/reporting into a CRM system built in php/mysql.

Consider the query:

 SELECT
    count(m.id) as student_count,
    SUM(if(m.membership_expiry >= "2021-06-30 00:00:00", 1, 0)) AS member_count,
    SUM(if(m.distance_from_college = "Unknown", 1, 0)) as unknown_count,
    SUM(if(m.distance_from_college = "Within 20 miles", 1, 0)) as w20_count,
    SUM(if(m.distance_from_college = "20-50 miles", 1, 0)) as 20t50_count,
    SUM(if(m.distance_from_college = "50-100 Miles", 1, 0)) as 50t100_count,
    SUM(if(m.distance_from_college = "100-200 miles", 1, 0)) as 100t200_count,
    SUM(if(m.distance_from_college = "200+ miles", 1, 0)) as 200p_count,
    SUM(if(m.country != "GB", 1, 0)) as int_count
FROM
    cps_booking AS b
LEFT JOIN 
    cps_member AS m ON b.user_id = m.id AND m.deleted = "0000-00-00 00:00:00"
LEFT JOIN
    cps_product AS p ON b.product_id = p.id AND p.deleted = "0000-00-00 00:00:00"
WHERE
    b.status = "Confirmed" AND
    b.deleted = "0000-00-00 00:00:00" AND
    p.type != "On-Demand" AND p.type != "Consultation" AND
    p.start_date BETWEEN "2021-06-01 00:00:00" AND "2021-06-30 23:59:59"

This is working so far to return the desired statistics but I am struggling for the best way to identify new students. For the purposes of this report a new student would be any user_id for whom the current record would be their first entry in the cps.booking table.

Any clever solutions?

Answer

Easiest is probably an uncorrelated subquery as part of the select. Here is an example of implementation.

select
    ....
    ....,
    (select count(user_id) from cps_booking group by user_id having count(*)=1)
from
   .... 

This is assuming you’re only concerned with cps_booking table and don’t have duplicated records for a given user_id. A couple of notes if helpful:

MySQL evaluates conditions to 1 or 0. That means you could just do SUM(m.country != "GB") instead of SUM(if(m.country != "GB", 1, 0))

I also noticed you split the conditions for cps_product between left join and where clause. You might want to move them over to the left join so you don’t end up with an inner join by proxy.