CODEWITHSUNDEEP
sql
I am not a bot
I am not a bot

Reputation: 33

SQL - I need to use the where keyword but it throws errors

I've been practicing SQL online and I'm stuck on this problem. I need to use the WHERE keyword but it doesn't include the null value if I use it. When I don't use it, it throws an error for incorrect number of rows.

Here's my code:

SELECT categories.name, SUM(order_items.price) AS revenue
FROM categories LEFT JOIN products
ON categories.id = products.category_id
JOIN order_items
ON products.id = order_items.product_id
JOIN orders
ON orders.id = order_items.order_id
WHERE orders.year between 2016 AND 2018
GROUP BY categories.name
ORDER BY revenue DESC;

Here's the question:

Select the name of each product category along with the revenue (name that column revenue) that it generated between 2016 and 2018. Sort the results by revenue in descending order.

And here are the tables:

categories(id, name)
order_items(id, order_id, product_id, quantity, price)
orders(id, client_id, year, total_price)
products(id, name, category_id, price)

Here's what the output should look like (sort of, I don't have the row with the value houseware in it because it's revenue is NULL and won't show up):

      name       | revenue |
 -----------------+---------+
 office supplies |   30600 |
 electronics     |   23000 |
 games           |   11000 |
 clothes         |    3450 |
 teas            |    2500 |
 beauty products |    1650 |
 party supplies  |    1350 |

EDIT: It's an online sql exercise, so the code is checked online, I'm not sure what server it's using - just that the language is sql. The error I got when I used the WHERE statement is wrong number of rows.

The error I get when I just take out the WHERE clause is: Data from the query is not correct or is in wrong order.

EDIT 2: I got all the rows thanks to the first comment but the null is still the first row of the table and it has to be the last row because they're considering null to be the lowest and the revenue is in descending order.

Upvotes: 1

Views: 46

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

When I don't use it, it throws an error for incorrect number of rows.

That's because you threw away the condition from WHERE, rather than moving it into the ON clause of the corresponding join:

SELECT categories.name, SUM(order_items.price) AS revenue
FROM categories
LEFT JOIN products
    ON categories.id = products.category_id
LEFT JOIN order_items
    ON products.id = order_items.product_id
LEFT JOIN orders
    ON orders.id = order_items.order_id and orders.year between 2016 AND 2018
GROUP BY categories.name
ORDER BY CASE WHEN revenue IS NULL THEN 1 ELSE 0 END, revenue DESC;

This way the NULLs obtained through left join are left undisturbed.

Upvotes: 2

Related Questions