mySQL Query Help

Okay, I am trying to write a query where I want to display a string depending on whether an integer value is low or high. For example, If the amount of money the Dad has in the family database is < 50, I want to list his name, 'Dad', and add the string 'running low on money'. If I count the amount of money for another member in the family and the value is >= 50, the query would add the message 'has lots of money'.

So, my query table would, I think, look something like this:

name | COUNT(money)

Mom | has lots of money
Dad | running low on money
Billy | running low on money
Judy | has lots of money


Any suggestions on how to do this? I do not want to display the INT value, just a choice of two different strings. Each string depends on the COUNT of the column with an INT data type.

Thanks for any help,
msae

Found solution. Assume have two tables, name_tbl and money_tbl. I created a derived table to do my count so I don't have to display the value in queried table. I used a UNION to list two different results, < 5 and >= 5.

SELECT name, 'running low on money'
FROM name_tbl n, (SELECT member_id, COUNT(money) ttl_money
FROM money_tbl GROUP BY member_id) mny_count
WHERE n.member_id = mny_count.member_id AND ttl_money < 5
UNION
SELECT name, 'has lots of money'
FROM name_tbl n, (SELECT member_id, COUNT(money) ttl_money
FROM money_tbl GROUP BY member_id) mny_count
WHERE n.member_id = mny_count.member_id AND ttl_money >= 5;

If anyone wanted to know, that is how I did it, I think I could use an IF statement as well to do this but thought of this way first.

It will look something like the following. Also, you are using COUNT, you should use SUM if you want the total amount of money

Code:

---


SELECT
  name,
  CASE WHEN SUM(money) > 5
    THEN 'lots of money'
    ELSE 'running low'
  END
FROM
  mytable
GROUP BY
  member_id;

---

Hey I like the way you wrote that, very nice and neat. I will try and write the same query that way. Thanks