I want to divide two Counts in a single query, but the DATE_TRUNC causes some issues

So far I have a query in postgre looking like this:

SELECT DATE_TRUNC('month', "Date") as date, 
COUNT(*) as AllTransactions,
COUNT(*) filter (where "State"='ACCEPTED') as Accepted,
FROM "Acceptance_Report"
GROUP BY 1
ORDER BY 1

It returns this:

Now I need to get the percentage, so it should be Accepted/AllTransactions*100

I understand that I could create another table and use INSERT , but I feel like there is another easy way to do it in a single query.

Do you have any ideas?

CodePudding user response：

So if you want to divide them, repeat the expressions. But it's important to convert one of the counts to a numeric value, otherwise it would result in an integer division (where 1/5 yields 0)

SELECT DATE_TRUNC('month', "Date") as date, 
       COUNT(*) as AllTransactions,
       COUNT(*) filter (where "State"='ACCEPTED') as Accepted,
       COUNT(*)::numeric  / COUNT(*) filter (where "State"='ACCEPTED') as pct
FROM "Acceptance_Report"
GROUP BY 1
ORDER BY 1

If you don't want to repeat the expressions, you can use a derived table:

select "date", 
       alltransactions, 
       accepted, 
       alltransactions::numeric / accepted as pct
FROM (
  SELECT DATE_TRUNC('month', "Date") as date, 
         COUNT(*) as AllTransactions,
         COUNT(*) filter (where "State"='ACCEPTED') as Accepted
  FROM "Acceptance_Report"
  GROUP BY 1
  ORDER BY 1
) t

CodePudding user response：

You can use AVG() aggregate function over the boolean expression "State" = 'ACCEPTED' converted to 1 for true and 0 for false:

SELECT DATE_TRUNC('month', "Date") AS date, 
       COUNT(*) AS AllTransactions,
       COUNT(*) filter (where "State" = 'ACCEPTED') AS Accepted,
       100 * AVG(("State" = 'ACCEPTED')::int) AS percentage
FROM "Acceptance_Report"
GROUP BY 1
ORDER BY 1;

See a simplified demo.