Is the Function in the GROUP BY Clause Necessary?-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

No. I don't even think the second one will run since your GROUP BY doesn't match your SELECT (excluding the aggregate fields such as COUNT(*)