Percentage showing as NULL after using NULLIF() - How to show 100% and 0%?-CodePudding

A helpful answer here showed me the NULLIF function to avoid the division by 0/0 error but now the percentages still show as NULL when one figure is 0. I have attached the code below and an image of the output. Is there a simple way around this?

SELECT 
  a.Game,
  IFNULL(SUM(a.ps_global), 0) AS ps_sales,
  IFNULL(SUM(a.xbox_global), 0) AS xbox_sales,
  SUM(a.ps_global / NULLIF((a.xbox_global   a.ps_global), 0) * 100) AS ps_pct,
  SUM(a.xbox_global / NULLIF((a.ps_global   a. xbox_global), 0) * 100) AS xbox_pct

FROM 
  aka-data-analysis-test.games.DF_A AS a

INNER JOIN
  aka-data-analysis-test.games.DF_B AS b
  ON a.Game = b.Name

GROUP BY 
  a.Game

Output

Thank you all

CodePudding user response：

B C will be null if either of them is null, so a way could be:

SUM(a.ps_global / NULLIF((IFNULL(a.xbox_global,0)   IFNULL(a.ps_global,0)), 0) * 100) AS ps_pct,

However, I think in that situation you probably want to do SUM(A)/SUM(B C) rather than SUM(A/(B C)) because SUMS of ratio are rarely meaningful.

So a clean way would be:

SAFE_DIVIDE(SUM(a.ps_global),SUM(IFNULL(a.xbox_global,0)   IFNULL(a.ps_global,0))) AS ps_pct

And if you want the division to be 0 instead of null you can do:

IFNULL(SAFE_DIVIDE(SUM(a.ps_global),SUM(IFNULL(a.xbox_global,0)   IFNULL(a.ps_global,0))),0) AS ps_pct