mySQL Query Help

[msae]

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

[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.

[dannystommen]

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

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

[msae]

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