Oracle SQL group by to_char - not a group by expression

Question

I want to group by dd-mm-yyyy format to show working_hours per employee (person) per day, but I get error message ORA-00979: not a GROUP BY expression, when I remove TO_CHAR from GROUP BY it works fine, but that's not I want as I want to group by days regardless hours, what am I doing wrong here?

    SELECT   papf.person_number emp_id,
         to_char(sh21.start_time,'dd/mm/yyyy') start_time,
         to_char(sh21.stop_time,'dd/mm/yyyy') stop_time,
         SUM(sh21.measure) working_hours
    FROM per_all_people_f papf,
         hwm_tm_rec sh21
         
         

    WHERE ...

    GROUP BY 
         papf.person_number,
         to_char(sh21.start_time,'dd/mm/yyyy'),
         to_char(sh21.stop_time,'dd/mm/yyyy')

    ORDER BY sh21.start_time

Please edit your question to include the full and exact error message, ensuring that code shown is the ***exact*** code that generated the error message. Then try a simplified query to debug your issue for example; `SELECT to_char(sh21.start_time,'dd/mm/yyyy') FROM hwm_tm_rec sh21 GROUP BY to_char(sh21.start_time,'dd/mm/yyyy') LIMIT 1` — MatBailie, Jan 18 '23 at 18:21
Not that it's required but SQL standers to support joins were changed in [1992](https://en.wikipedia.org/wiki/SQL-92) to use different join syntax. Usually makes reading SQL easier and easier to troubleshoot... — xQbert, Jan 18 '23 at 18:29
you are right, I usually use ```LEFT JOIN``` syntax, but this query is copy-pasted and will be refactored after completion. — Aasem Shoshari, Jan 18 '23 at 18:31
It's the order by clause, you need to use the to_char expresssion here — Andrew, Jan 18 '23 at 18:33
just if it says something like ```not a selected expression``` would be much better... — Aasem Shoshari, Jan 18 '23 at 18:37
It means that the expression in the ORDER BY isn't an expression (or derived from an expression) present in the GROUP BY, which it ***must*** be, if it's not an aggregate. I agree that it should say what LINE the "not a group by expression" is on, but not to change the terminology. — MatBailie, Jan 18 '23 at 18:41
I don't know that any RDBMS will tell you what column(s) are missing from the group by, or where it is (or isn't?) in the query. The actual verbiage "not a group by expression" could be clearer, I guess. — Andrew, Jan 18 '23 at 18:59
For those who noted non-ANSI join: Oracle Business Suite uses it for decades, they do not change anything in view definitions even in recent versions and everything is joined via comma and awkward `(+)`. For example [PAY_BANK_ACCOUNTS](https://docs.oracle.com/en/cloud/saas/human-resources/23a/oedmh/paybankaccounts-6680.html#paybankaccounts-6680). It may be a habit to do the same — astentx, Jan 18 '23 at 19:16

score 0 · Answer 1 · answered Jan 18 '23 at 19:21

ORDER BY sh21.start_time

needs to either be just the column alias defined in the SELECT clause:

ORDER BY start_time

or use the expression in the GROUP BY clause:

ORDER BY to_char(sh21.start_time,'dd/mm/yyyy')

If you use sh21.start_time then the table_alias.column_name syntax refers to the underlying column from the table and you are not selecting/grouping by that.

Oracle SQL group by to_char - not a group by expression

1 Answers1