How To Sum Count Result?

Question

I have a database that will count daily total amount of customer that does or doesn't have a transactions.

Customer Column is a varchar data type

Here is how my table looks like :

Here is how my querying looks like :

As You can see, I used Case When Query to filtering whenever the customer had more than 1 Transactions, It should be count as 1 Transactions.

Here are the codes if you prefer to look a query instead of Screenshot :

SELECT dates,
    CASE WHEN COUNT(customer) > 1 THEN COUNT(customer) - COUNT(customer) + 1
    ELSE COUNT(customer)
    END AS Valid_Transactions
FROM transactions
WHERE revenue != 0 AND sales ILIKE ('Bel%')
GROUP BY dates,customer
ORDER BY dates ASC

The result will be like this :

dates         valid_transactions
"2022-06-05"                   1 
"2022-08-05"                   1
"2022-09-05"                   1
"2022-09-05"                   1
"2022-09-05"                   1

So, my question is

How can I sum The Case result based on dates? So it will be looks like this :

dates        valid_transactions
"2022-06-05"                  1
"2022-08-05"                  1
"2022-09-05"                 17
"2022-10-05"                 20
"2022-11-05"                 16 

I can't filtering the customer with GROUP BY and COUNT only because there are bunch of similar customers doing a transactions at exact same day and at the other days

Answer 1

You could use your query as a subquery:

SELECT 
    dates, SUM(Valid_Transactions) Valid_Transactions
FROM
    (SELECT 
         dates,
         CASE 
             WHEN COUNT(customer) > 1 
                 THEN COUNT(customer) - COUNT(customer) + 1
             ELSE COUNT(customer)
         END AS Valid_Transactions
     FROM transactions
     WHERE revenue != 0 
       AND sales ILIKE ('Bel%')
     GROUP BY dates, customer
     ORDER BY dates ASC) t
GROUP BY 
    dates