How to Group By a Date with Specified Conditions and Row Count-CodePudding

I have table which is named os_txn.pay and I have columns merchant_no and insert_date_time. What I am looking for is that I want to know numbers of rows, I will give merchant_no as a parameter and will give value in java code, and I also want to look at insert_date_time. My purpose is that how many rows in the insert_date_time with a specified merchant_no? I tried some query below but it didnt give results that I wished. I think it should be inclued count(*) and group by but looks like I couldnt succeed in the syntax. I am open your suggestions from now, thank you.

SELECT COUNT(*)
  FROM os_txn.pay
 WHERE merchant_no = :merchantNoValue --it takes merchantNoValue FROM service request in java
   AND insert_date_time = :insert_date_time
 GROUP BY insert_date_time, merchant_no

I want a result like a single cell data for example Count = 9. For example our insert_date_time is 20221009 and in this insert_date_time my specified merchant_no has 9 rows of data. I want to know this data, I hope I could express myself clearly, thank you from now

I think I found my solution :

SELECT COUNT(*)
  FROM (SELECT COUNT(*)
          FROM os_txn.pay
         WHERE merchant_no = :merchantNoValue
         GROUP BY insert_date_time, merchant_no)

CodePudding user response：

One option is to handle without using a subquery through nesting directly the COUNT(*) within a SUM() aggregation such as

SELECT SUM(COUNT(*))
  FROM os_txn.pay
 WHERE merchant_no = :merchantNoValue 
 GROUP BY insert_date_time, merchant_no

CodePudding user response：

Your WHERE condition specifies the values of both merchant_no and insert_date_time:

WHERE merchant_no = :merchantNoValue AND insert_date_time = :insert_date_time

That means that GROUP BY statement is not needed here. After the WHERE condition is applied, there will only be one group. If I am understanding correctly, this is what you need:

SELECT COUNT(*)
  FROM os_txn.pay
 WHERE merchant_no = :merchantNoValue --it takes merchantNoValue FROM service request in java
   AND insert_date_time = :insert_date_time