SQL Interview Question - 6

Consider a table named sales_data with the following schema:

CREATE TABLE sales_data (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    region VARCHAR(50)
);

Assume the table contains the following data:

id	amount	region
1	100.00	East
2	200.00	West
3	NULL	North
4	150.00	East
5	120.00	NULL
6	180.00	West
7	NULL	NULL
8	300.00	South

Write a SQL query to calculate the total sales amount for each region, including NULL regions, and display the result in descending order of the total sales amount. If a region is NULL or if there are NULL values in the amount column, treat them as 0 in the calculation.

---

Solution

SELECT 
    COALESCE(region, 'Unknown') AS region,
    COALESCE(SUM(COALESCE(amount, 0)), 0) AS total_sales
FROM 
    sales_data
GROUP BY 
    region
ORDER BY 
    total_sales DESC;

Explanation:

• We use the COALESCE function to handle NULL values. If region or amount is NULL, it's replaced with 'Unknown' or 0 respectively.
• The SUM function calculates the total sales amount for each region.
• The GROUP BY clause groups the results by region.
• The ORDER BY clause sorts the result set in descending order of total sales amount.

 

Share your alternate solution in comments.