Data analysis with NULL value in the table SQL

Question

The objective here is to find customers whose purchasing habit changes over time in terms of change of percentage of certain order category in total num of order categories in corresponding year (especially decreasing trend). I have worked out the a table containing columns like

CUSTOMERNAME, ORDERCATEGORY, 2014PERCENT, 2015PERCENT, 2016PERCENT

However, since in each year there are some customers who haven't buy anything, there are some NULL value in the table. So I was wondering in this case (every individual shows different order pattern), how could I find a way to calculate something like differences of percentage in different years? Or is there any other way for me to tackle it?

Answer 1

You can use the COALESCE() function to turn the NULL values to 0:

SELECT CUSTOMERNAME, ORDERCATEGORY,
       COALESCE(2014PERCENT, 0) as 2014PERCENT,
       COALESCE(2015PERCENT, 0) as 2015PERCENT,
       COALESCE(2016PERCENT, 0) as 2016PERCENT
FROM t;

For most analytical purposes, zeroes are more appropriate than NULLs in summary data.

Answer 2

You ca try using IS NULL to check whether it has null values or not.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;