Count distinct on more than one columns 1

[Rajesh99]

I have a table like:
orderid, custname, zipcode, orderdate.
I want to count to do like this:
select orderdate, count(distinct custname,zipcode)
from a_able
group by orderdate.
Bu this does not work.


I am trying to get for an order how many distinct custname were their in in zip code.

Alternative is to do:
select orderdate, zipcode, count(distinct custname)
from a_table group by orderdate.
But this can not be used as I have some other settings in the program. Any hints?

 

[SantaMufasa]

I am trying to get for an order how many distinct custname were their in in zip code.

Rajesh, I am missing something here. Taken literally, for a particular order, is there not just one customer name and one ZIP code? An alternate interpretation of your request could be:

...For a given order's ZIP code, how many other customers are in that ZIP code...

Is that what you want? I need you to please clarify what you are looking for.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Rajesh99]

No I have same customer name in zip code. Here is an example:

zip code Name order#

20800 - Tom 100
20800 - Tom 100
20800 - Tom 200
20800 - Jack 300
20800 - Jack 400
ziocode distinct count(order by name)
20800 4

In other words I have 4 unique order for Tom&order#. This is waht I want to dsipaly by sql. sql can do distinct count on one column, I want to do distinct over a more than one columns.
Can this be done by sql or I need to do work around.

 

[SantaMufasa]

Rajesh,

I am sure that we can deliver code to do what you want...I just need to ensure that I understand clearly what you want. The problem is that your data do not seem to match the description of what you want:

...I have 4 unique order for Tom&order#.

1) You have 2 unique order for Tom&order#.
2) You have 4 unique orders for ZIP code 20800.
3) You have 2 unique customer names for orders in ZIP code 20800.


Do you want one of the three alternatives, above, or do you want something still different? Once I am clear on your needs, I can produce a result that matches your needs from your data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Rajesh99]

Thanks. I have really four unqiue records in above, i have some other coumns in the result set and I do not care for them. I want to have in result set for each zip code list of unique NAME+ORDER#:

20800 - Tom 100
20800 - Tom 200
20800 - Jack 300
20800 - Jack 400

The temp tables and count/join etc. TIf things are done straight in SQL it should be much faster as I am looking at millions of records here so creating temo table can be expensive.

 

[SantaMufasa]

So, Rajesh, do you want your output to appear as:

Option 1:

20800         4

or do you want the output to appear as:

Option 2:

20800 - Tom    100
20800 - Tom    200
20800 - Jack   300
20800 - Jack   400

???

If Option 1, then you really don't care about the Customer Name. If Option 2, then you just want unique order numbers and the Customer Name for each order.

Which would you like?



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Rajesh99]

Option 1 will work. Thanks.

 

[SantaMufasa]

Rajesh,

You can achieve Option 1 with this code:

SQL> col a heading "ZIP|Code" format a5
SQL> col b heading "Order #" format 999
SQL> select ZIP a, Ord b from rajesh2
  2  /

ZIP
Code  Order #
----- -------
20800     100
20800     100
20800     200
30800     200
20800     300
20800     400

6 rows selected.

SQL> select zip a,count(distinct ord) b
  2  from rajesh2
  3  group by zip
  4  /

ZIP
Code  Order #
----- -------
20800       4
30800       1

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Rajesh99]

Sory my set was too simple, how will the following data set work:

20800 - Tom 100
20800 - Tom 100
20800 - Tom 200
20800 - Jack 100
20800 - Jack 300
20800 - Jack 400
ziocode distinct count(order by name)
20800 5

I like to get 5 as distinct count for Name/order# combination. Your query will return only in this case.

 

[Rajesh99]

Sorry my set was too simple, how will the following data set work:

20800 - Tom 100
20800 - Tom 100
20800 - Tom 200
20800 - Jack 100
20800 - Jack 300
20800 - Jack 400
20820 - Jack 100
ziocode distinct count(order #, name)
20800 5
20820 1

I like to get distinct count for Name/order# combination, not jsut on order#.

 

[SantaMufasa]

Then how about this code:

select * from rajesh2;

ZIP   CUSTN        ORD
----- ----- ----------
20800 Tom          100
20800 Tom          100
20800 Tom          200
20800 Jack         100
20800 Jack         300
20800 Jack         400
20820 Jack         100

7 rows selected.

col a heading “ZIP” format a5
col b heading “Count|Name/|Order|Sets” format 999
select zip a,count(distinct custname||ord) b
  from rajesh2
 group by zip
/

      Count
      Name/
      Order
ZIP    Sets
----- -----
20800     5
20820     1

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]