sql use sum result in query clause

Question

select acc.user_id
     , member.nickname
     , sum(credit) as ttl 
     , member.balance
  from account_history as acc
  left 
  join member
    ON acc.user_id = member.user_id
 where balance > 0 
   and balance <50
 group 
    by user_id

i would like to display row which ttl ( sum(credit) ) > 0 ,

so i change to

select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50 and ttl > 0
group by user_id

but it turns error . no column ttl found

Possible duplicate of [Using column alias in WHERE clause of MySQL query produces an error](https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — Nick, Mar 26 '19 at 07:33
I don't see a functional dependency here so I'm surprised this works at all. What version of MySQL are you using? And if you want help developing a more legitimate query, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Mar 26 '19 at 07:37
On a side note: When working with more than one table you should qualify all columns. `group by user_id` for instance is ambiguous, because you could either refer to `acc.user_id` or `member.user_id`. As the DBMS doesn't throw an error (as it should in my opinion) your results are kind of left to chance. — Thorsten Kettner, Mar 26 '19 at 08:01
And on another side note: It's always preferable to explain your tables and especially tell us about unique, primary and foreign keys. Here it seems, that there exists some user table we don't see (hence a column named `user_id`) and the table `member` is somehow related to it. Then there is an `account_history` related to a user and for some reason that we cannot know you expect to find `account_history` rows without a matching `member` row (hence the outer join). We might have been able to give better advice, had you described the tables' relation. — Thorsten Kettner, Mar 26 '19 at 08:07

score 2 · Answer 1 · answered Mar 26 '19 at 07:32

use the value in the having clause should work:

select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc 
    left join member ON acc.user_id=member.user_id 
where balance > 0 and balance <50 
group by user_id 
having  ttl > 0

in where clause you can not use aliasses

Also see https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

score 2 · Answer 2 · answered Mar 26 '19 at 07:32

You should use HAVING statement for filtering by aggregate fields

SELECT 
  acc.user_id,
  member.nickname, 
  sum(credit) as ttl,
  member.balance
FROM account_history AS acc left join member ON acc.user_id = member.user_id 
WHERE balance > 0 and balance <50
GROUP BY user_id 
HAVING ttl > 0

score 1 · Answer 3 · edited Mar 26 '19 at 08:12

1

i think it will work,

Select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50 and sum(credit) > 0
group by user_id

or you can also use having clause for same result.

like,

Select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50
group by user_id 
having sum(credit) > 0

edited Mar 26 '19 at 08:12

Thorsten Kettner

89,309
7
49
73

answered Mar 26 '19 at 07:53

Dilip Shukla

7
2

I think this is the correct solution, it will definitely work. – Soumen Pasari Mar 26 '19 at 08:04
I've formatted the queries for you. It's easy, you just have to press the {} button to get the code formatted. – Thorsten Kettner Mar 26 '19 at 08:14
Your first query is wrong. In the `WHERE` clause you have access only to columns. Aggregation occurs later, so in order to limit results on `sum(credit)` you need the `HAVING` clause (or an outer query accessing the aggregation results). The `WHERE` clause is not an option. – Thorsten Kettner Mar 26 '19 at 09:14

score 0 · Answer 4 · answered Mar 26 '19 at 07:34

use having and acc.user_id,member.nickname,member.balance in group by

SELECT 
  acc.user_id,
  member.nickname, 
  sum(credit) as ttl,
  member.balance
FROM account_history AS acc left join member ON acc.user_id = member.user_id 
WHERE balance > 0 and balance <50
GROUP BY acc.user_id, member.nickname,member.balance
HAVING ttl > 0

score 0 · Answer 5 · edited Mar 26 '19 at 07:35

0

You need use sub query to get ttl column as it is derived column

select * from
(select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50
group by user_id 
)bal
Where ttl>0

edited Mar 26 '19 at 07:35

Jens

67,715
15
98
113

answered Mar 26 '19 at 07:34

saravanatn

630
5
9

sql use sum result in query clause

5 Answers5