Question 89959 in PostgreSQL Question posted in
The official documentation can be found here.

Divide two counts in one selection (with date_trunc) – Postgresql

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:

Date AllTransactions Accepted
2019-01-01 930 647
2019-02-01 840 589

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search