| Query Help !!!! [message #374229] |
Thu, 31 May 2001 08:52  |
Arpit
Messages: 99
Registered: March 2001
|
Member |
|
|
Hi ,
Can somebody please help me with the query.
I'm trying to get a distinct count of clients with the followin query. The problem is I'm getting a different count while having the date_created field in the select statement and doing group by. While as in the other query which is the same if I remove the date_created and group by it gives me a different count.
Query1:
-----------------------------------------------------
select count(distinct(a.client_no)),to_char(date_created,'YYYY')
from licence a
where a.date_created between to_date('01-JAN-1987','DD-MON-YYYY') and
to_date('31-DEC-2001','DD-MON-YYYY')
and a.client_no=(select distinct(b.client_no) from client b
where a.client_no=b.client_no
and b.country_code like 'CA')
group by to_char(date_created,'YYYY')
-------------
Output:-
-------------
COUNT(DISTINCT(A.CLIENT_NO)) TO_C
---------------------------- ----
680 1994
320 1995
841 1996
904 1997
823 1998
1056 1999
1273 2000
129 2001
The total is 6026
Query2:- (Which is almost same as query1):-
-------------------------------------------------
select count(distinct(a.client_no))
from licence a
where a.date_created between to_date('01-JAN-1987','DD-MON-YYYY') and
to_date('31-DEC-2001','DD-MON-YYYY')
and a.client_no=(select distinct(b.client_no) from client b
where a.client_no=b.client_no
and b.country_code like 'CA')
-----------------------------------------------------
Output:-
---------
COUNT(DISTINCT(A.CLIENT_NO))
----------------------------
4572
Can somebody please help me as to what would be going wrong in the query. I would like to have the count same.
Thanks in advance.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
