Click here to Skip to main content
15,919,434 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I have an issue with the query and I am struggling to figure out why. I'll paste the query and the error below. Thanks in advance for your help!

SQL
SELECT
              day,
              count(*) AS due_total,
              count(id) filter(where status = 'early') AS dispatched_early,
              count(id) filter(where status = 'on_due_date') AS dispatched_on_due_date,
              count(id) filter(where status = 'late') AS dispatched_late,
              count(id) filter(where status = 'not_yet_dispatched') AS not_yet_dispatched,
              (count(id) filter(where status in ('early', 'on_due_date')) / count(*)) AS otd_percent
            FROM
              (
                SELECT
                  soh.id,
                  date(soh.original_estimated_dispatch_date) AS day,
                  date(sosh.date_time_stamp) AS dispatch_date,
                  CASE
                    WHEN date(sosh.date_time_stamp) < date(soh.original_estimated_dispatch_date) THEN 'early'
                    WHEN date(sosh.date_time_stamp) = date(soh.original_estimated_dispatch_date) THEN 'on_due_date'
                    WHEN date(sosh.date_time_stamp) > date(soh.original_estimated_dispatch_date) THEN 'late'
                    WHEN date(sosh.date_time_stamp) IS null THEN 'not_yet_dispatched'
                  END AS status
                FROM
                  liab.sales_order_header soh
                  left join liab.sales_order_summary sos ON sos.sales_order_header_id = soh.id
                  left join liab.sales_order_status_history sosh ON sosh.id = sos.dispatch_printed_id
                WHERE
                  soh.customer_id not like "327%" -- test orders
                  AND sos.current_status_id != 19 -- cancelled status
                  AND YEAR(soh.original_estimated_dispatch_date) = YEAR(NOW() )
                  AND DATE(soh.original_estimated_dispatch_date) >= "2022-10-29"
                  AND DATE(soh.original_estimated_dispatch_date) != DATE(NOW())
              )
            GROUP BY
              day
            ORDER BY
                day ASC


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(where status = 'early') AS dispatched_early,\n              count(id) filter(whe' at line 4 (1064) (SQLExecDirectW)")


What I have tried:

I have checked for comas or typos errors
Posted
Updated 31-Oct-22 2:10am
Comments
Richard Deeming 31-Oct-22 7:17am    
count(..) filter(...) doesn't look like any SQL / MySQL syntax I've ever seen, and I can't see it documented:
MySQL :: MySQL 8.0 Reference Manual :: 3.3.4.8 Counting Rows[^]

Which version are you using, and where did you get that syntax from?
Member 15758970 31-Oct-22 8:10am    
Ahh that would make sense. I was using this syntax in Azure Databricks but I have no idea what dialect is that. Do you know if there is something reflecting this functionality in MySQL?
Richard Deeming 31-Oct-22 8:26am    
For MS SQL Server, I'd generally use:
SUM(CASE WHEN «condition» THEN 1 ELSE 0 END)

I suspect something similar should work in MySQL.
Member 15758970 31-Oct-22 8:45am    
Thank you!

1 solution

FILTER was added to the SQL spec in 2003, but it is barely supported even today - I believe it might be in the latest version of MySql, but it isn't part of Sql Server at all: The FILTER clause: Selective Aggregates[^]
 
Share this answer
 
Comments
Member 15758970 31-Oct-22 8:46am    
Thanks a lot!
OriginalGriff 31-Oct-22 8:57am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900