Forum Moderators: open
Table A. Call it customers, friends, whatever.
rec_id
unique_id
first
last
(etc.)
Table B holds the "credits" for the entities, joins on unique_id, there can be any number of rows, and the credits can vary in amount.
rec_id
unique_id
amount
Easy enough to sum() the amounts for a given entity from table A.
How do I query a count(*) on the number of people with credits > 0? Doh!
Example, table B,
1¦1234¦0.00
2¦1234¦1.50
3¦1234¦0.25
4¦1235¦0.00
5¦1236¦2.50
The query should return a single row, a count of 2: users 1234 and 1236 have credits > 0. My meager attempts are returning multiple rows.
<feelin' sharp tonight . . . >
select count(distinct unique_id) from table_b where status=0 and
date_add(credit_date, interval 30 day) < curdate() having sum(amount) > 0;
select count(*) from table_b
where status=0 and
date_add(credit_date, interval 30 day) < curdate() and
amount > 0
I don't think the HAVING clause gives you what you want
How do I query a count(*) on the number of people with credits > 0?
A sum of the column credits table is required to see if they have credits. So there can be entries for credits, but the sum may be zero.
Using DISTINCT unique_id should solve the problem with with zero amounts:
select count(DISTINCT unique_id) from table_b
where status=0 and
date_add(credit_date, interval 30 day) < curdate() and
amount > 0
