Show 0 value if data has null value in data studio-CodePudding

i have a data like this:

date          total       status
2020-01-01    100         NOT OK
2020-01-02    100         OK
2020-01-03    200         NOT OK
2020-01-04    300         OK

I am using data studio line chart to show total value that has OK status based on the date with expected result like this:

date          total       
2020-01-01     0        
2020-01-02    100        
2020-01-03     0         
2020-01-04    300

i have been implement the data in data studio line chart but only show the date has value with OK status like this:

date          total       
2020-01-02    100        
2020-01-04    300

how the way to show the date and total value if any data has no value like i write before ? how to handle it ?

CodePudding user response：

Created a

CodePudding user response：

You can use a CASE statement to get the required result:

SELECT date,(CASE WHEN status="OK' THEN Total END) AS TotalA
FROM table
ORDER BY date;