Welcome to WebmasterWorld Guest from 54.226.183.49

Forum Moderators: open

Message Too Old, No Replies

How would you do this?

Sum suminy sum sum sum sum charoo

     

rocknbil

3:49 am on Jan 17, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I'm at the end of a long day and have this major mind block . . . I'm sure it's staring me in the face but it's just not happening.

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

rocknbil

3:58 am on Jan 17, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



This works. I guess. :-P

select count(distinct unique_id) from table_b where status=0 and
date_add(credit_date, interval 30 day) < curdate() having sum(amount) > 0;

syber

1:47 am on Jan 18, 2010 (gmt 0)

10+ Year Member



try this:

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

rocknbil

7:46 pm on Jan 18, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Thank you . . . but the problem is, that returns multiple rows.

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.

syber

10:14 pm on Jan 18, 2010 (gmt 0)

10+ Year Member



SELECT COUNT(*) without a GROUP BY should always return only one row.

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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month