How to calculate percent in a filter-CodePudding

I'm trying to calculate the percentage in my filter column.

Here is my fiddle https://www.db-fiddle.com/f/hY1JFUwk3YNGYye345pny8/8

My base table :

----------------------
| id | active | dept |
----------------------
| 1  | true   | AFG  |
| 2  | true   | AFG  |
| 3  | true   | AFG  |
| 4  | true   | POD  |
| 5  | true   | POD  |
| 6  | true   | KMN  |
| 7  | true   | AGO  |
| 8  | true   | AGO  |
| 9  | false  | AGO  |
| 10 | true   | AGO  |
| 11 | true   | AGO  |
| 12 | true   | SUD  |
| 13 | true   | SUD  |
| 14 | true   | MOL  |
----------------------

My current request :

SELECT
    'Active Request' AS Title,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'true'
UNION
SELECT
    'Inactive Request' AS Title,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'false'
UNION
SELECT
    'Inactive Request' AS Title,
    ((COUNT(*)/TOTAL::float)*100) FILTER (WHERE dept = 'AFG') AS AFG,
    ((COUNT(*)/TOTAL::float)*100) FILTER (WHERE dept = 'AGO') AS AGO,
    ((COUNT(*)/TOTAL::float)*100) FILTER (WHERE dept = 'KMN') AS KMN,
    ((COUNT(*)/TOTAL::float)*100) FILTER (WHERE dept = 'MOL') AS MOL,
    ((COUNT(*)/TOTAL::float)*100) FILTER (WHERE dept = 'POD') AS POD,
    ((COUNT(*)/TOTAL::float)*100) FILTER (WHERE dept = 'SUD') AS SUD,
    '100' AS TOTAL
FROM req
WHERE active = 'false'

Error I'm getting :

Query Error: error: syntax error at or near "FILTER"

My goal is to retreive data like :

------------------------------------------------------------------------------
| Title                      | AFG  | AGO  | KMN | MOL | POD  | SUD  | TOTAL | 
------------------------------------------------------------------------------
| Active Request             | 3    | 4    | 1   | 1   | 2    | 2    | 13    |
| Inactive Request           | 0    | 1    | 0   | 0   | 0    | 0    | 1     |
| Active Request (percent)   | 21,4 | 28,6 | 7,1 | 7,1 | 14,3 | 14,3 | 92,8  |
| Inactive Request (percent) | 0    | 7,1  | 0   | 0   | 0    | 0    | 7,1   |
------------------------------------------------------------------------------

I tried using ((COUNT(*)/"TOTAL"::float)*100) FILTER (WHERE dept = 'MOL') AS MOL, to calculate the percentage.

CodePudding user response：

I think you cant divide count like you do. Check this

Here solution :

    SELECT
    'Active Request' AS Title,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'true'
UNION
SELECT
    'Inactive Request' AS Title,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'false'
UNION
SELECT
    'Inactive Request (percentage)' AS Title,
    CAST(((select COUNT(*) from req where active = 'false' and dept = 'AFG') * 100)/count(*)AS DECIMAL(7,2))AS AFG ,
    CAST(((select COUNT(*) from req where active = 'false' and dept = 'AGO') * 100)/count(*)AS DECIMAL(7,2))AS AGO,
    CAST(((select COUNT(*) from req where active = 'false' and dept = 'KMN') * 100)/count(*)AS DECIMAL(7,2))AS KMN,
    CAST(((select COUNT(*) from req where active = 'false' and dept = 'MOL') * 100)/count(*)AS DECIMAL(7,2))AS MOL,
    CAST(((select COUNT(*) from req where active = 'false' and dept = 'POD') * 100)/count(*)AS DECIMAL(7,2))AS POD,
    CAST(((select COUNT(*) from req where active = 'false' and dept = 'SUD') * 100)/count(*)AS DECIMAL(7,2))AS SUD,
    CAST(((select COUNT(*) from req where active = 'false') * 100)/count(*)AS DECIMAL(7,2))AS SUD
FROM req
UNION
SELECT
    'Active Request (percentage)' AS Title,
    CAST(((select COUNT(*) from req where active = 'true' and dept = 'AFG') * 100)/count(*)AS DECIMAL(7,2))AS AFG ,
    CAST(((select COUNT(*) from req where active = 'true' and dept = 'AGO') * 100)/count(*)AS DECIMAL(7,2))AS AGO,
    CAST(((select COUNT(*) from req where active = 'true' and dept = 'KMN') * 100)/count(*)AS DECIMAL(7,2))AS KMN,
    CAST(((select COUNT(*) from req where active = 'true' and dept = 'MOL') * 100)/count(*)AS DECIMAL(7,2))AS MOL,
    CAST(((select COUNT(*) from req where active = 'true' and dept = 'POD') * 100)/count(*)AS DECIMAL(7,2))AS POD,
    CAST(((select COUNT(*) from req where active = 'true' and dept = 'SUD') * 100)/count(*)AS DECIMAL(7,2))AS SUD,
    CAST(((select COUNT(*) from req where active = 'true') * 100)/count(*)AS DECIMAL(7,2))AS TOTAL
FROM req

Here is example how make percentage:

CAST(CAST(((select COUNT(*) from req where active = 'false' and dept = 'AGO') * 100)as DECIMAL(7,2))/cast(count(*)AS DECIMAL(7,2)) as DECIMAL(7,2))AS AGO

And you get 7.14