How to ignore rows with zero value in SQL-CodePudding

I'm trying to show the results only if the total price is more than 0.

This is my DB table:

-----------------------------------------------------------
|  id  |     name       |   phone    |   price   | status |
-----------------------------------------------------------
|  1   |      John      | 0701111111 |    300    |   1    |
-----------------------------------------------------------
|  2   |      Lucy      | 0702222222 |    500    |   1    |
---------------------------------------------------------
|  3   |      Adam      | 0703333333 |    700    |   0    |
-----------------------------------------------------------
|  4   |      Lucy      | 0702222222 |   -500    |   -1   |
-----------------------------------------------------------
|  5   |      John      | 0701111111 |    450    |   1    |
-----------------------------------------------------------
|  6   |      Mike      | 0704444444 |    150    |   1    |
-----------------------------------------------------------

I'm trying to show only the total price of each paying person by distinct phone and status (1 = paid, -1 = refund, 0 = not payed)

Here is my SQL code:

SELECT
DISTINCT(presents_test.phone) AS uniquePhone,
(SELECT MIN(name) FROM presents_test WHERE phone = uniquePhone AND status != 0) AS name,
(SELECT SUM(price) FROM presents_test WHERE phone = uniquePhone AND status != 0) AS totalPrice
FROM presents_test
WHERE status != 0

The result is:

-----------------------------------
| uniquePhone | name | totalPrice |
-----------------------------------
| 0701111111  | John |    750     |
-----------------------------------
| 0702222222  | Lucy |     0      |
-----------------------------------
| 0704444444  | Mike |    150     |
-----------------------------------

I just need to ignore the Lucy row because it's 0 price.

I've tried to add AND totalPrice > 0 to WHERE clause but it gets an error of unknown column.

What can i do to solve this?

CodePudding user response：

You can group by the phone number. When you group then aggregation functions like SUM apply to each group. And HAVING is the WHERE clause for grouped data

SELECT phone AS uniquePhone,
       MIN(name) AS name,
       SUM(price) AS totalPrice
FROM presents_test
WHERE status <> 0
GROUP BY phone
HAVING SUM(price) > 0