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

How to add percentages to an output – Postgresql

I would like to add a relative percentage to an SQL output

Here is the code:

SELECT customer_status.status AS "Status of Customer",         
       COUNT (customer_bio),     
FROM customer_status     
JOIN customer_bio     
  ON customer_status.status_id=customer_bio.status_id     
GROUP BY status

Here is the output

Status Number
Switched Off 352
Out of Town 743
Not reachable 564
Active 13738
Relocated 78

I want to add percentage on another column where by switched off is 352/total number * 100

I tried this:

SELECT customer_status.status AS "Status of Customer",
       count(customer_bio)
       count(customer_bio)/count(*) * 100 AS "Percentage
FROM customer_status
JOIN customer_bio
  ON customer_status.status_id=customer_bio.status_id
GROUP BY status

|nd this is what I get:

Status Number Percentage
Switched Off 352 100
Out of Town 743 100
Not reachable 564 100
Active 13738 100
Relocated 78 100

Please assist. I use postgresql via azure data studio

Tags:

2

Answers


  1. 0

    Both COUNT(customer_bio) and COUNT(*) give you the same output because they’re both aggregated on the same partition (GROUP BY status).

    You can try using window functions instead. Since window functions compute values for each record, you can then remove duplicate rows with the DISTINCT operator.

    SELECT DISTINCT customer_status.status AS "Status of Customer",
                COUNT(customer_bio) OVER(w)
                COUNT(customer_bio) OVER(w) / COUNT(customer_bio) OVER() * 100 AS "Percentage
FROM       customer_status
INNER JOIN customer_bio
        ON customer_status.status_id=customer_bio.status_id
GROUP BY status
WINDOW w AS (PARTITION BY status)
    Login or Signup to reply.
  2. 0

    please try this way:

    declare @total_count as real

select @total_count =count(*) from customer_bio

SELECT customer_status.[status] AS 'Status of Customer',
       count(customer_bio),
       count(customer_bio)/@total_count * 100 AS 'Percentage'
FROM customer_status
JOIN customer_bio
  ON customer_status.status_id=customer_bio.status_id
GROUP BY [status]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search