| Count Distinct PL/SQL [message #255963] |
Thu, 02 August 2007 10:17  |
tllocke
Messages: 22
Registered: March 2006
|
Junior Member |
|
|
|
I'm sure this'll be an easy question for someone. I have a query that returns all the payments made to clients (one client can have multiple), for a given date range (usually fiscal). In the report I need to count the distinct clients, and given that I need all their individual payments, I can't do the COUNT(Distinct field) in the query. The summary column won't allow me to specify to count just distinct, and a formula column don't recognize COUNT as a PL/SQL function (because it's not, I know this, but I'm trying anything). Any ideas on how I can achieve this? If I haven't been clear enough, please let me know and I'll try again. Thanks.
|
|
|
|
| Re: Count Distinct PL/SQL [message #256016 is a reply to message #255963] |
Thu, 02 August 2007 14:26  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Try again 
I didn't understand why can't you use a FORMULA column; why wouldn't it recognize COUNT function?
As I've understood it, there are (at least) two parameters: par_start_date and par_end_date.
Your main query looks likeSELECT client_id, purchase_date, product_name, price
FROM some_table
WHERE purchase_date BETWEEN :par_start_date AND :par_end_date;
Number of distinct clients is independent of this query (at least I think so) and should be calculated separately: either using another query (which doesn't have to be joined to the main one), or using a formula column. Whichever it is, query would be the same (formula column example): l_cnt_clients NUMBER;
BEGIN
SELECT COUNT(DISTINCT client_id)
INTO l_cnt_clients
FROM some_table
WHERE purchase_date BETWEEN :par_start_date AND :par_end_date;
RETURN (l_cnt_clients);
END;
If I misunderstood the question, could you (really) try again? This time with sample data and expected result. Please, do not upload XLS or DOC files; do it using proper formatting with the [code] tags.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
