Count distinct does not work in Oracle SQL Developer

Question

I need to count distinct date in one column but I do not want to group by that.

Datasource table:

ID   zip5    date       volume  
11   11111   01/03/16   5  
12   11111   01/03/16   6
13   11111   01/04/16   7

My code:

Select zip5, count(ID), count(distinct (date)), sum(volume), 
from table
group by zip5

Desired output:

zip5   count(ID)  count(distinct (date))  sum(volume)
11111          2                       1           11
11111          1                       1            7

However, what I got is:

zip5   count(ID)  count(distinct (date))  sum(volume)
11111          3                       3           18

You've tagged this for three competing database products. Are you using all three? — Darwin von Corax, Mar 16 '16 at 22:25
If you group by zip5, you can't expect something different, the expected result (with the input you're giving) is the line 11111 3 3 18. — Paolof76, Mar 16 '16 at 22:33
You've also tagged it for PL/SQL Developer, but have SQL Developer in the title. Not that the client matters here, but it would help if you were clear about which client and database you were actually using. — Alex Poole, Mar 16 '16 at 22:35
To expand on @Paolof76's comment, if you include the `date` column in the `GROUP BY` you may get the result you are expecting. — Rob Paller, Mar 16 '16 at 22:37
You do not want to group by it? But that will give you the answer you need! — Nick.Mc, Mar 17 '16 at 01:11

score 1 · Answer 1 · answered Mar 16 '16 at 23:37

1

Most likely, the problem you are facing is that your date column has a time component -- and, alas, it is not shown when you select from the table.

You can truncate the date to remove the time component. So try this:

Select zip5, count(ID), count(distinct trunc(date)), sum(volume), 
from table
group by zip5;

answered Mar 16 '16 at 23:37

Gordon Linoff

1,242,037
58
646
786

Linoff. Thank you very much!. It works. Why the date data format will matter? You mean like minute/second which not show in data? – Michael Li Mar 17 '16 at 13:12
@MichaelLi . . . Correct. Oracle does not, by default, show the time component of a `date` column. – Gordon Linoff Mar 18 '16 at 02:42

score 0 · Answer 2 · answered Mar 16 '16 at 22:42

To get your desired output simple add the "DATE" column in the GROUP BY expression.

Select zip5, count(ID), count(distinct ("DATE")), sum(volume)
from "TABLE"
group by zip5,"DATE"
order by 1,2;

      ZIP5  COUNT(ID) COUNT(DISTINCT("DATE")) SUM(VOLUME)
---------- ---------- ----------------------- -----------
     11111          1                       1           7 
     11111          2                       1          11

You may group by (i.e. select in disctinct rows) even columns that are not in the select list.

Count distinct does not work in Oracle SQL Developer

2 Answers2