homepage Welcome to WebmasterWorld Guest from 54.211.97.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
How would you do this?
Sum suminy sum sum sum sum charoo
rocknbil

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



 
Msg#: 4062261 posted 3:49 am on Jan 17, 2010 (gmt 0)

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

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



 
Msg#: 4062261 posted 3:58 am on Jan 17, 2010 (gmt 0)

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

10+ Year Member



 
Msg#: 4062261 posted 1:47 am on Jan 18, 2010 (gmt 0)

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

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



 
Msg#: 4062261 posted 7:46 pm on Jan 18, 2010 (gmt 0)

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+ Year Member



 
Msg#: 4062261 posted 10:14 pm on Jan 18, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved