Skip to content

Is the Function in the GROUP BY Clause Necessary?

I’m an Oracle newbie just trying to learn.

Are the following two queries equivalent in terms of their results?

Query 1

SELECT
    COUNT( customers.id ) AS "id",
    customers.full_name AS "name",
    customers.cty AS "country",
    TO_CHAR( customers.date, 'mm/dd/yyyy' ) AS "date"

FROM customers

GROUP BY
    customers.full_name,
    customers.cty,
    TO_CHAR( customers.date, 'mm/dd/yyyy' );

Query 2: No TO_CHAR() in the GROUP BY Clause

SELECT
    COUNT( customers.id ) AS "id",
    customers.full_name AS "name",
    customers.cty AS "country",
    TO_CHAR( customers.date, 'mm/dd/yyyy' ) AS "date"

FROM customers

GROUP BY
    customers.full_name,
    customers.cty,
    customers.date;

I’m using Oracle version 12.1.

Answer

Both queries won’t return the same result… unless customers.date includes only dates with no time part. The DATE type in Oracle includes the date part and also the time part. So it’s equivalent to a time stamp.

The first query groups by whole days, while the second one groups by date/hour/minute/second.