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

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:

Please assist. I use postgresql via azure data studio

CodePudding user response：

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)

CodePudding user response：

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]