SQL Error

[Dan Munns]

Hi all,

I am trying to create a widget to show the Top 10 used categories when logging an incident (last calendar month). The SQL is probably horrible but bear with me:

SELECT COUNT (h_category) AS 'Category'
FROM h_itsm_requests 
WHERE (MONTH (h_datelogged) = MONTH(CURDATE()) -1)
AND (h_fk_team_name = 'TEAM 1' or 
     h_fk_team_name = 'TEAM 2' or 
     h_fk_team_name = 'TEAM 3' or 
     h_fk_team_name = 'TEAM 4' or 
     h_fk_team_name = 'TEAM 5') 
AND (h_fk_priorityname = 'P1' or 
     h_fk_priorityname = 'P2' or 
     h_fk_priorityname = 'P3' or 
     h_fk_priorityname = 'P4') 
AND h_requesttype = 'incident' 
AND h_status <> 'status.cancelled'
AND h_category IS NOT NULL
GROUP BY h_category LIMIT 10

Now if I remove the 'COUNT' it displays the top 10 in the category list alphabetically, if I add count it fails with an error 'denied to user' 

I am assuming that the 'COUNT' function is denied to users but cant see why. 

So is there anyway to do this? 

@Victor @Bob Dickinson  

[Victor]

What error is that?

[Dan Munns]

@Victor error is:

MySQL Error 1370: execute command denied to user 'qLrEySa5uTnPzVj'@'%' for routine 'stb.COUNT'

 

[Victor]

Remove that space...

COUNT (h_category)

[Dan Munns]

Done and it now works (thanks @Victor)

But I cant get this to work: 

GROUP BY h_category LIMIT 20 
ORDER BY COUNT(h_pk_reference) DESC

(I changed the count to h_pk_reference at the top of the statement) 

It is the ORDER BY which makes it fail. I have tried ORDER BY COUNT, ORDER BY h_pk_reference, ORDER BY Number Logged (the alias) all with no joy. 

I just get a syntax error.  

[Dan Munns]

Cancel that. 

LIMIT needs to be last. :facepalm: 

[Victor]