Subquery error 1064 (Count distinct number)

Grafana MySQL
,
1

Hello everyone, I am using Grafana v8.4.3.

I am trying to get the number of distinct order numbers from the table (TAB_MaterialLocations) from my database in MariaDB.

I first get all the order numbers which meet my requirements (including many repetitive order numbers) from the table then group by order number, and count the number of the district ones by using a subquery.

However, I always get an error message like below:

db query error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘GROUP BY ML_Ordernumber’ at line 14

and the output should count how many distinct numbers are there instead of how many times each order number is repeated respectively.

I do not know where the problem is. It would be much appreciated if someone can help on that. Thank you!!

Here is my query:

SELECT
ML_Ordernumber AS “order number”,
COUNT(ML_Ordernumber) AS “# of order numbers”
FROM
(SELECT
ML_Ordernumber AS “OrderNumber”,
ML_Latest_date AS “Time”
FROM TAB_MaterialLocations
WHERE
(ML_Ordernumber LIKE “MO%” AND ML_MaxContainer > “208” AND ML_MaxStatus = “9” AND ML_Part_Type != “FB” AND ML_Part_Type != “RW” AND $__timeFilter(ML_Latest_date))
)
GROUP BY ML_Ordernumber

2

does this query work if you connect directly to your MariaDB instance and try it there?

3

Hi Matt, thank you for your reply! I didn’t try that but I found my mistakes and now it works in Grafana. The problem was I did not select the name I assigned in the inner query in the outer one and did not give a name to the inner query. Thank you for your help :grinning:

1 Like