I am trying to get count of distinct status count for each date available. Here is my table:
| RequestDate | TransactionStatus |
|---|---|
| 2022-01-25 23:59:54 | 2994 - Payment method selected |
| 2022-01-25 23:59:29 | 3000 - Payment failed |
| 2022-01-25 23:59:05 | 0000 - Payment Processed Successfully |
| 2022-01-25 23:58:15 | 0000 - Payment Processed Successfully |
based on this table, how can I get total number of records under distinct TransactionStatus for each day from RequestDate column?
Expected result will be:
| TransactionStatus | RequestDate | Count |
|---|---|---|
| 2994 - Payment method selected | 2022-01-25 | 1 |
| 3000 - Payment failed | 2022-01-25 | 1 |
| 0000 - Payment Processed Successfully | 2022-01-25 | 2 |
I tried
select RequestDate,TransactionStatus, Count(*)
from QPayTransactions
group by 1,2
but am getting following error;
Each GROUP BY expression must contain at least one column that is not an outer reference.
CodePudding user response：
You may aggregate by date and status and then take the count:
SELECT TransactionStatus, CAST(RequestDate AS date) AS RequestDate,
COUNT(*) AS cnt
FROM QPayTransactions
GROUP BY TransactionStatus, CAST(RequestDate AS date);
Demo
CodePudding user response：
select TransactionStatus,CAST(RequestDate AS DATE) RequestDate, Count(*) Count
from QPayTransactions
group by 1,2