0

I have two tables. Now I have this query:

SELECT
    p.date,
    HOUR(start) as startHour,
    (p.material * MIN(r.performance)) as energy
FROM
    PLANNING p
JOIN
    PERFORMANCE r ON p.`objectId` = r.`objectId` AND p.`mold` = r.`mold`
WHERE
    p.date = '2018-12-05'
GROUP BY
    startHour, p.material
ORDER BY
    startHour

This query gives me back multiple rows per hour, obviously, because i have multiple objectId and mold couples. Indeed I need to have one row per hour with the sum of energy, so i've tried in this way:

SELECT
    p.date,
    HOUR(start) as startHour,
    SUM(p.material * MIN(r.performance)) as energy
FROM
    PLANNING p
JOIN
    PERFORMANCE r on p.`objectId` = r.`objectId` and p.`mold` = r.`mold`
WHERE
    p.date = '2018-12-05'
GROUP BY
    startHour
ORDER BY
    startHour

but i get Invalid use of group function

Shidersz
  • 16,846
  • 2
  • 23
  • 48
MarioC
  • 2,934
  • 15
  • 59
  • 111
  • Multiple rows per hour has little to do with objectId and moldId; it is because you have multiple materials. The latter query's issue is that you cannot nest aggregate (group) functions; you must use subqueries for that kind of scenario. Also, some configurations will not allow selecting p.data without grouping by it as well. – Uueerdo Dec 05 '18 at 20:21
  • Oddly though mySQL seems to support it... https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by So now I'm not sure what the problem is. – xQbert Dec 05 '18 at 20:42
  • I think, your problem is that you have the option [ONLY_FULL_GROUP_BY](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by) enabled, and `p.material` is not a column declared on the `GROUP BY` clause and not also an aggregate. When you made the groups, there will be more than one `p.material` in each group. So, you have to use an aggregate method to select one, or `ANY_VALUE(p.material)` to choice a random one. – Shidersz Dec 05 '18 at 21:35
  • Also, try replacing this `SUM(p.material * MIN(r.performance)) as energy` by this one: `SUM(p.material) * MIN(r.performance)` since `N * (a + b + c)` is equal to `(N * a + N * b + N * c)`. That change will make the second query to work. – Shidersz Dec 05 '18 at 21:40
  • If you're still struggling, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Dec 05 '18 at 22:31
  • thanks @D.Smania but i still have the rows not grouped by startHour but n rows for every hour – MarioC Dec 05 '18 at 22:34

0 Answers0