CODEWITHSUNDEEP
mysqlsqlmariadb
user3233787
user3233787

Reputation: 377

MySQL SUM only if one of the columns contain a certain value

Is there a way to sum rows only when one of the columns has a certain value? I have this table:

| order_id | total | is_tip |
-----------------------------
|     1    |  12   |    1   |
|     1    |   3   |    0   |
|     2    |  28   |    0   |
|     2    |  15   |    0   |
|     3    |  39   |    1   |
|     3    |   8   |    1   |

The desired result would be:

| order_id | SUM(total) |
-------------------------
|     1    |     15     |
|     3    |     47     |

It would only sum up if one of the is_tip columns is equal to 1. So order IDs 1 and 3 are qualified. So far I've tried to use this query:

SELECT order_id, SUM(total) FROM orders HAVING COUNT(is_tip) >= 1;

But this query is so wrong. I'm just a beginner. Thank you in advance.

Upvotes: 1

Views: 751

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5141

Please use below query,

SELECT order_id, SUM(total) FROM orders where is_tip >= 1 group by order_id;

Having can be used only where you want to filter on aggregation basis

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Use a having clause:

SELECT order_id, SUM(total)
FROM orders
GROUP BY order_id
HAVING SUM(is_tip) >= 1;

Your query also needs a GROUP BY. And I'm assuming you want a row with at least one "tip". You can also phrase the HAVING clause as:

HAVING MAX(is_tip) > 0

Upvotes: 1

Related Questions