Hello :-)
This morning I got quite shocked after I found out that my COUNT(*) wasn't behaving as I supposed it should do.
I managed to create a simple SELECT that shows the unexpected behaviour.
Can anyone explain me what is happening here? Is it a bug? Is it happening to you too?
My actual workaround is to use SUM(1).
WITH T1 AS (
SELECT 'ROBERT' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'TOM' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'JOHN' NAME, 'CANADA' COUNTRY FROM DUAL),
T2 AS (
SELECT SUM(1) SUM1,
COUNT(*) COUNT_STAR,
COUNT(DISTINCT NAME) COUNT_DISTINCT
FROM T1
GROUP BY COUNTRY
ORDER BY COUNTRY
)
SELECT * FROM T2;
SUM1 COUNT_STAR COUNT_DISTINCT
---------- ---------- --------------
1 1 1
2 1 2
I thought that SUM1 and COUNT_STAR should give the same result, but for the second row COUNT_STAR is 1 instead of 2.
It is not counting the rows.
Please note that if you write this query slightly differently then SUM(1) and COUNT(*) give the same result.
Example, WITH removed:
WITH T1 AS (
SELECT 'ROBERT' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'TOM' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'JOHN' NAME, 'CANADA' COUNTRY FROM DUAL)
SELECT SUM(1) SUM1,
COUNT(*) COUNT_STAR,
COUNT(DISTINCT NAME) COUNT_DISTINCT
FROM T1
GROUP BY COUNTRY
ORDER BY COUNTRY
Example Order By removed
WITH T1 AS (
SELECT 'ROBERT' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'TOM' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'JOHN' NAME, 'CANADA' COUNTRY FROM DUAL),
T2 AS (
SELECT SUM(1) SUM1,
COUNT(*) COUNT_STAR,
COUNT(DISTINCT NAME) COUNT_DISTINCT
FROM T1
GROUP BY COUNTRY
)
SELECT * FROM T2;
Example COUNT DISTINCT removed
WITH T1 AS (
SELECT 'ROBERT' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'TOM' NAME, 'USA' COUNTRY FROM DUAL UNION ALL
SELECT 'JOHN' NAME, 'CANADA' COUNTRY FROM DUAL),
T2 AS (
SELECT SUM(1) SUM1,
COUNT(*) COUNT_STAR
FROM T1
GROUP BY COUNTRY
ORDER BY COUNTRY
)
SELECT * FROM T2;
DB is
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production under Linux.
bye
Andrea