Adding Percent Column Based on Combination of 2 columns (Postgresql)-CodePudding

I am having trouble with a query where I would appreciate your feedback.

I need to add a 'percentage' column (rounded to 2 decimal places) which calculates the % of total for each animaltype and size combination.

These are the selected columns from animals table:

animaltype (sorted alphabetically)
size (sorted in order: small, medium, large)
total

Here is the code I have for summary query:

SELECT animaltype, size, SUM(total) AS Total FROM animals
WHERE sponsored_animalid IS NULL
GROUP BY animaltype, size
ORDER BY animaltype, size DESC;

This is how the sample output should look like:

Percentage needs to be calculated for each animaltype and size combination.

Sample formula would be for example (animaltype: bird, size: small):

ROUND((total for Bird(Small) / SUM of total for all birds (small, medium, large) * 100) , 2) = 12.55% (refer to the sample output above)

I dont want to create a new table/view, just need a way to fix a percent column for the query output.

Thank you for your help.

Appreciate your feedback.

CodePudding user response：

You can use a window function to compute this value. For example:

SELECT
  animaltype, 
  size, 
  SUM(total) AS Total,
  100.0 * SUM(total) / SUM(total) OVER(partition by animaltype) AS percent
FROM animals
WHERE sponsored_animalid IS NULL
GROUP BY animaltype, size
ORDER BY animaltype, size DESC;

CodePudding user response：

I suggest to you this query :

SELECT
  t1.animaltype,
  t1.size,
  SUM(t1.total) AS total,
  round(SUM(t1.total / t2.total) * 100, 2) percent
FROM
  animals t1
  INNER JOIN (
    SELECT
        animaltype,
        SUM(total) total
    FROM
        animals
    GROUP BY
        animaltype
   ) t2 ON t1.animaltype = t2.animaltype
WHERE
   t1.sponsored_animalid IS NULL
GROUP BY
  t1.animaltype,
  t1.size
ORDER BY
  t1.animaltype,
  t1.size DESC;