Division in group by SQL table-CodePudding

I have the following SQL code

SELECT (COUNT(*) filter (WHERE has_a)) AS count_a, 
COUNT(*) AS total_count,
process_date
FROM(SELECT process_date::date AS process_date,
    (CASE WHEN (columnA > 0) THEN true ELSE false END) AS has_a
    FROM my_table)
temptable
GROUP BY process_date
LIMIT 5;

which gives the following table

I want to create a column called percent_a with value (count_a/total_count)*100 grouped by process_date. E.g for row 1 the new column would have value 49.4 i.e. (1030/2085)*100.

I have tried

SELECT process_date,
    ((COUNT(*) filter (WHERE has_a))/COUNT(*) * 100) AS percent_a,
    FROM(SELECT process_date::date AS process_date,
        (CASE WHEN (columnA > 0) THEN true ELSE false END) AS has_a,
         FROM my_table)
temptable
GROUP BY process_date
ORDER BY process_date DESC
LIMIT 1;

But this just gave 0s.

How can I create the column to display the % I want? I think something is happening with the GROUP BY but I don't know how to fix it.

CodePudding user response：

It's because count returns an integer, you just need to cast it:

SELECT process_date,
    (((COUNT(*) filter (WHERE has_balance))::DOUBLE PRECISION)/COUNT(*) * 100) AS percent_a,
    FROM(SELECT process_date::date AS process_date,
        (CASE WHEN (columnA > 0) THEN true ELSE false END) AS has_a,
         FROM my_table)
temptable
GROUP BY process_date
ORDER BY process_date DESC
LIMIT 1;