Report Query Failed - mysql error 1111 Invalid Group Function

💬 General Discussion
1

I have a number of reports created using the GUI that are available in a dashboard tab.

I think that sometime in the past upgrades a problem came up.

The report query that is failing is to identify the number of meetings completed in the past week for each user. There is also a similar count of the tasks completed and calls completed. The task report has the same error. The call query is OK as far as I can tell.

For the meeting count, the error is:

Query Failed: SELECT COUNT(`meetings`.name) AS 'Number_of_meetings' FROM meetings  WHERE ( COUNT(`meetings`.name) IS NOT NULL AND  (  `meetings`.date_start BETWEEN "2018-04-02 00:00:00" AND "2018-04-09 00:00:00" AND `meetings`.status = 'Held' ) AND  meetings.deleted = 0  ): MySQL error 1111: Invalid use of group function

For the task count, the error is:

Query Failed: SELECT COUNT(`tasks`.created_by) AS 'Tasks_Created' FROM tasks  WHERE ( COUNT(`tasks`.created_by) IS NOT NULL AND  ( `tasks`.status = 'Completed' AND  `tasks`.date_modified BETWEEN "2018-04-02 00:00:00" AND "2018-04-09 00:00:00" AND `tasks`.modified_user_id = "1" ) AND  tasks.deleted = 0  ): MySQL error 1111: Invalid use of group function

The mysql error is something about an incorrect group function. The docs say something about creating a subquery. While I could probably figure this out if I was running the query from the command line, I’m using the GUI so I’m not sure how I should fix the report.

Can I fix this using the GUI?? Is so, how?

2

Hhhmm it looks like a bug, I don’t see a “group by” clause in that SQL, although there are “count” columns…

Can you please open an issue with this on GitHub, including the necessary steps to reproduce your query from the UI? Thanks

3

Bug submitted: https://github.com/salesagility/SuiteCRM/issues/5698

Thanks!

1 Like
4

Hi,
This line looks invalid

WHERE ( COUNT(meetings.name) IS NOT NULL

we can not apply NOT NULL on Count because it is not a text,it is a counter variable for now. Basically checking the NULL records should be handled separately.

The query should be

SELECT COUNT(meetings.name) AS ‘Number_of_meetings’ FROM meetings WHERE meetings.date_start BETWEEN “2018-04-02 00:00:00” AND “2018-04-09 00:00:00” AND meetings.status = ‘Held’ AND meetings.deleted = 0 AND meetings.name IS NOT NULL

Thanks

Sohaib Majeed
SuiteCRM Developer
Upwork

5

Thanks @Sohaib.

The report GUI created the query and so I’m not sure how to fix it within the GUI.

Your comment will definitely help fix the issue if you could you post this on github at the link in the bug post above?