Jump to content

SQL Error

Dan Munns

Recommended Posts

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 :) 

Link to comment
Share on other sites

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. :( 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...