0

I have a table that I am trying to get the percent using the CASE statement

SELECT "Agent",
(100* ( max(case when "Agent"= 'Agent1' then "TOTAL"  end)/
max(case when "Agent" = 'TOTAL'  then "TOTAL" end) ))
    as "Agent1%"
From "SumofAgent"
group by Agent

the Query shows a blank next to the new column. I am thinking it might be because the value is to small..is that possible?

My table look like this:

Agent       TOTAL
-----       -----
Agent1       13
Agent2       21
Agent3       49
TOTAL        1343

(13/1343)*100=0.96%

I have tried the case statements by them self's max(case when "Agent" = 'TOTAL' then "TOTAL" end) and max(case when "Agent"= 'Agent1' then "TOTAL" end). They are pulling the right values but when you try to do the math it does not show anything.

Using Zoho reports but will use PostgreSQL to test

coco minion
  • 123
  • 2
  • 15

1 Answers1

1

In the group 'Agent1' max(case when "Agent" = 'TOTAL' then "TOTAL" end) is null

And in the group 'TOTAL' max(case when "Agent"= 'Agent1' then "TOTAL" end) is null

All other groups have both null.

As a result every value in the result column is null on all result rows.

see example here:

How to use a SQL window function to calculate a percentage of an aggregate

Community
  • 1
  • 1
Jasen
  • 11,837
  • 2
  • 30
  • 48