I have a table in oracle

what I am looking for is to get a "Count of unique accounts that make at least one payment on any given day"

so far I have done this

select Count(DISTINCT "account_id") as count_accounts
from "payments " pay
WHERE "payment_date" = TO_DATE('01/10/2017','DD-MM-YYYY')
GROUP BY "account_id";

but I am getting null but I should be getting 1 as there is an account that made a payment on the given day. Not sure what I am doing wrong. Also is there a better way of writing this query?

P.S my date is stored as "10-05-20 17:00:00", how do I change it to simple "10-05-2017"?

CodePudding user response：

You do not want to use a GROUP BY "account_id" and you can filter on a date range:

SELECT Count(DISTINCT "account_id") as count_accounts
FROM   "payments " pay
WHERE  "payment_date" >= DATE '2017-10-01'
AND    "payment_date" <  DATE '2017-10-01'   INTERVAL '1' DAY;

(Note: by filtering on a range, Oracle is able to use an index on the "payment_date" column. If you TRUNC("payment_date") then Oracle will not use an index on the "payment_date" column; you would need a separate function-based index on TRUNC("payment_date").)

Which, for the sample data:

CREATE TABLE "payments " (payment_id, Status, "payment_date", "account_id", currency, amount) AS
SELECT '72b30af0-323e-4931-8fcc-2c08ad8d0b19', 'completed', DATE '2017-10-01', 2291969088, 'GBP', 10 FROM DUAL UNION ALL
SELECT '1b5341c0-508c-450f-a139-bc898f112fed', 'completed', DATE '2014-11-12', 7851880663, 'USD', 20 FROM DUAL

(Note: It is bad practice to use quoted identifiers as they are case sensitive and you need to use the quotes and the same case everywhere the identifier is used.)

(Note 2: It is really, really bad practice to put trailing spaces at the end of your identifiers as the spaces inside the quotes are part of the identifier and you will have to put exactly the same number of spaces at the end of every identifier [and they are much harder to count that visible characters].)

Which outputs:

COUNT_ACCOUNTS
1

db<>fiddle here

CodePudding user response：

I think may be you need convert the column

select Count(DISTINCT "account_id") as count_accounts 
from "payments " pay  
WHERE TO_CHAR("payment_date", 'DD/MM/YYYY') = '01/10/2017'
GROUP BY "account_id";

CodePudding user response：

I stored payment_date values again using insert values command with the desired format (DD/MM/YYYY) initially it was stored as (YY-MM-DD). Then I used the trunc command to get rid of the time stamp. Works fine for now I guess

select "account_id", Count(DISTINCT "account_id") as count_accounts
    from "payments " pay 
    WHERE TRUNC(payment_date) = TO_DATE('01/10/2017','DD/MM/YYYY') AND "Status" = 'completed'
    GROUP BY "account_id";