How to sum different elements in MySQL

Backend Development
1

I’m looking to present the number of times an event takes place. The database has an event_type column with different possible values. I want to count the number of times an event takes place within a specific time period.

I’m struggling here… My code so far is

`SELECT COUNT(1) AS dbCount,
            SUM(CASE WHEN event_type = "visit_completed" THEN 1 ELSE 0 END) AS visit,
            SUM(CASE WHEN event_type = "mood_observation" THEN 1 ELSE 0 END) AS mood,
            SUM(CASE WHEN event_type = "physical_health_observation" THEN 1 ELSE 0 END) AS healthObservation,
            SUM(CASE WHEN event_type = "regular_medication_taken" THEN 1 ELSE 0 END) AS medsTaken,
            SUM(CASE WHEN event_type = "regular_medication_not_taken" THEN 1 ELSE 0 END) AS medsNotTaken,
            SUM(CASE WHEN event_type = "alert_raised" THEN 1 ELSE 0 END) AS alert,
        FROM events
        WHERE timestamp REGEXP "2019-05-12";`

I’m getting the error 1064. I’ve tried deleting the different sections, but it looks like there may be a problem with the SUM(CASE WHEN …) syntax in MySQL?

Heres a sample of the return

SUM(CASE WHEN event_type = "visit_completed" THEN 1 ELSE 0 END),\n' +
    '            SUM(CASE WHEN event_type = "mood_observation" THEN 1 ELSE 0 END),\n' +
    '            SUM(CASE WHEN event_type = "physical_health_observation" THEN 1 ELSE 0 END),\n' +
    '            SUM(CASE WHEN event_type = "regular_medication_taken" THEN 1 ELSE 0 END),\n' +
    '            SUM(CASE WHEN event_type = "regular_medication_not_taken" THEN 1 ELSE 0 END),\n' +
    '            SUM(CASE WHEN event_type = "alert_raised" THEN 1 ELSE 0 END),\n' +

Could the \n breaks be causing a problem?

Help is appreciated.

2

One comma. Was all it took! I just had to delete the comma before the FROM events section! Groans :frowning_with_open_mouth: