[SOLVED] Only show rows with a group sum of greater than zero?

[PeerFly]

The code below works fine.

 

select distinct(u.userid), u.*, sum(e.amount) as amnt from `users` as u left join `earnings` as e on u.userid = e.referral where u.referrer = '$userid' group by u.userid ORDER BY amnt DESC LIMIT $from, $max_results

 

Now, if I want to ONLY display rows where amnt is GREATER THAN zero, it doesn't work. How can I make it a statement on a column that was just created from a sum?

 

This doesn't work:

 

select distinct(u.userid), u.*, sum(e.amount) as amnt from `users` as u left join `earnings` as e on u.userid = e.referral where u.referrer = '$userid' and amnt > 0 group by u.userid ORDER BY amnt DESC LIMIT $from, $max_results

 

This seems like a really simple problem but I can't figure it out.

 

[fenway]

DISTINCT is not a function.  Use group by.

[PeerFly]

Ok, but that doesn't fix my problem. I'm trying to only show rows where the amnt column is greater than zero but it's not working because the amnt column really isn't a physical column, just one that I made up on the fly from a sum. How can I make it work?

[J.Daniels]

sum(e.amount) is going to return the sum of the amount column for all returned rows.

 

Did you want to include only the rows that were greater than 0??  If so, change:

 

amnt > 0

 

to:

 

e.amount > 0

[PeerFly]

J, what the code does so far is it adds up all the users earnings from the database and then puts their totals into a new separate column called "amnt".

 

For instance, if Joe has 5 rows in the earnings table where each dollar value from the "amount" column sums up to $10.00, then by doing this code, it will distinctively display Joe one time, and then the new "amnt" column will show Joe's total sum of money. What I am trying to do is display ONLY the rows where "amnt" is greater than zero. The strange thing is that you can order by the temp amnt column, but you cannot create a "where" clause with it for some reason. There has to be a way.

[J.Daniels]

I understand now.  I haven't tested this, but you'll want to look at the HAVING clause:

 

select distinct(u.userid), u.*, sum(e.amount) as amnt from `users` as u left join `earnings` as e on u.userid = e.referral where u.referrer = '$userid' and amnt > 0 group by u.userid HAVING amnt > 0 DESC LIMIT $from, $max_results

[PeerFly]

You da man!