Use COALESCE within SUM in MySQL SELECT statement

Viewed 406

I have the following tables:

Table users
id  name     base_discount
1   jack     10
2   michael  20
3   richard  30

Table item
id  name     category_id  price
1   hammer   1            10
2   knife    2            15
3   spoon    2            12
4   plate    3            20
5   tree     4            400

Table category
id  name
1   tools
2   kitchen
3   dishes
4   garden

Table discount_category
id  user_id  category_id  discount
1   1        1            20
2   1        3            25
3   3        3            10
4   1        2            15

Table discount_item
id  user_id  item_id  discount
1   2        1        50
2   1        2        50

Now what I want to achieve. I want to attach the discount per item that a user has to the correct item. If that is not available (NULL) I want to attach the discount per category that a user has. And if that is not available (NULL), I want to attach the base discount that a user has. With the discount I then calculate the new price of the item. However, when I try using COALESCE() within SUM() I get a syntax error. What am I doing wrong?

Below is my current query:

SELECT item.id, item.name, category.id, 
category.name AS category_name, item.price, SUM((100 -
COALESCE(
(
    SELECT discount_item.discount
    FROM discount_item
    INNER JOIN users ON discount_item.user_id = users.id
    WHERE users.id = '1' AND discount_item.item_id = item.id
),
(
    SELECT discount_category.discount
    FROM discount_category
    INNER JOIN users ON discount_category.user_id = users.id
    WHERE users.id = '1' AND discount_category.category_id = item.category_id
),
(
    SELECT users.base_discount
    FROM users
    WHERE users.id = '1'
)
)) / 100 * item.price) AS new_price,
FROM item
INNER JOIN category ON item.category_id = category.id;

Please also see the below link for an SQL Fiddle (couldn't do it on sqlfiddle.com as it wouldn't load). In the example I have appended a suffix to each table name. http://rextester.com/LCCKSD59098

1 Answers
Related