[SOLVED] SQL-Error 1064 Grouping by USER-Element when Field-Element Calculation Sum

[gustl]

I have a normal Field-Element called 'urlaubstag', only integer-values are allowed. In the List-View-Settings I have select 'Sum' for Calculations. In the same list I use the User-Element too (it shows the user-email). So far no problems. But when I want to group by this user-email I always get the error-message 1064:

"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 ') AS label FROM `fab_urlaub` LEFT JOIN `jo3_users` AS `jo3_users` ON `jo3_users' at line 2 SQL=SELECT SUM(value) AS value, label FROM (SELECT DISTINCT `fab_urlaub`.`id`, fab_urlaub.urlaubstag AS value, CONCAT(jo3_users_0.) AS label FROM `fab_urlaub` LEFT JOIN `jo3_users` AS `jo3_users` ON `jo3_users`.`id` = `fab_urlaub`.`id_benutzer` LEFT JOIN `jo3_users` AS `jo3_users_0` ON `jo3_users_0`.`id` = `fab_urlaub`.`id_chef` WHERE ( `jo3_users`.`id` = 607 )) AS t GROUP BY label"

I think the CONCAT-Statement is incomplete, the correct statement should be " ... CONCAT(jo3_users_0.id) ...".
Has anybody an idea to solve this issue?

 

[troester]

It's really a user element, not a dbjoin to #_users with maybe anything (a blank) in the concat field?

 

[gustl]

It's really a user element, not a dbjoin to #_users with maybe anything (a blank) in the concat field?

Hi Troester, thanks for your reply. It's a normal User-element. Without the calculation about the field 'urlaubstag' the grouping works fine.

 

[gustl]

The only calculation this error occurs is when I choose 'Sum' in the List-View-Settings for Calculations. With 'AVERAGE', 'COUNT' etc. everything's okay. Because of this I thought I could make a workaround by choosing the 'Custom-Calculation' in the settings with the expression 'SUM(%s)'. But unfortunately the result is always the total sum, that means filters or groupings are left out. No solution available?

 

[troester]

I think I've found the issue
https://github.com/Fabrik/fabrik/pull/1324

 

[gustl]

Hi Troester,
that's it! Work's fine now, you're the greatest! Thanks you so much for your speedy assistance

 

[cheesegrits]

I haven't merged this PR yet, commented on it. I'm just trying to understand why the difference between sum() and avg(), as to which $groupby array we use.

-- hugh

 

[Monicaaraujo]

Hello,
I'm new to databases, have limited php skills, but have a long experience in computing in general, networking, etc. I also code but Im a beginner in arduino and processing. So, please help me.
I have a very similar problem but could not follow the instructions you provided above for a different but similar problem.

I have a form where I need users to fill the amount of hours/days/time for each category of course, each category will have its weight, and after all 10 pages (one category per page), have the total.

I love what I can do with Fabrik already. But I'm sure there are something wrong with what I'm doing because I can't get rid of an error 1064 (I am new to Joomla and php but have large experience in computing and networking, and Im a beginner programmer in arduino and processing. Please I need help with a problem that is happening. Every time I use the cal plugin something goes wrong and it happens an error as:
Error

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 'AS value, label FROM (SELECT DISTINCT `faculty_log`.*, faculty_log.my_semester_h' at line 1 SQL=SELECT AS value, label FROM (SELECT DISTINCT `faculty_log`.*, faculty_log.my_semester_hour_equivalent AS value, 'calc' AS label FROM `faculty_log` LEFT JOIN `gg0vp_users` AS `gg0vp_users` ON `gg0vp_users`.`id` = `faculty_log`.`teacher_name` ) AS t GROUP BY label

I see you said something about grouping by id and I tried to fix it by removing the user plugin from the elements, but the error persists. It reports the error to the front end and I'd also liked to change that.
Please help me. ;-)