How to Group by Current day and Count Rows in PLSQL?-CodePudding

Hello I have table "os_txn.pay_link" and inside there are many columns. What I want to do is that I want to count the row numbers by looking at "merchant_id" column for the current day. So for example what I am looking for an output is that today one of "merchant_id" has "8" rows. So I want to know the number of rows of the "merchant_id" column for current day. I think I should use count(*) in view with select statement but couldnt succeed about syntax. So I am open your suggestions thank you.

CodePudding user response：

If I understood you correctly, a simple option would be

select merchant_id, count(*)
from os_txn.pay_link 
where date_column = trunc(sysdate)
group by merchant_id;

presuming that date_column contains date only (i.e. for today, 8th of October 2022, that's its value - no hours, minutes or seconds).

If date column contains time component, again - a simple option - would be

select merchant_id, count(*)
from os_txn.pay_link
where trunc(date_column) = trunc(sysdate)
group by merchant_id;

If there's an index on date_column, then such a code wouldn't use it (unless it is a function-based index) so you'd rather modify it to

where date_column >= trunc(sysdate)
  and date_column <  trunc(sysdate   1)

If that's not it, do post sample data and desired result.