3 Must-Know SQL Functions For Efficient Data Analysis

Original Source Here

1. Coalesce

Consider a case where we need to left join the sales table to the products table. They are related by the product codes so we join the tables based on this column.

SELECT P.*, S.storecode, s.date, s.salesqty, s.salesrevenue                                                                      FROM products P                                                                                                                             LEFT JOIN sales S                                                                                                                           ON P.productcode = S.productcode;
(image by author)

Not every product has sales on the given date. For these products, the columns from the sales table contain null values (empty in the screenshot above).

We can use the coalesce function to handle null values as a result of joining tables. In our case, we can fill the sales quantity and sales revenue columns with zeroes. The date column can be filled with the date in the other rows.

coalesce(s.date, '2021-05-10') as date,
coalesce(s.salesqty,0) as salesqty,
coalesce(s.salesrevenue, 0)as salesrevenue FROM Products P LEFT JOIN Sales S ON P.productcode = S.productcode;
(image by authot)

The empty cells are filled with the specified values in the coalesce function. I have left the store code as empty.


Trending AI/ML Article Identified & Digested via Granola by Ramsey Elbasheer; a Machine-Driven RSS Bot

%d bloggers like this: