Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Aggregating Data: help in making 3 queries become 1

stiffmaestrostiffmaestro Member Posts: 16
I have a table..say 'Members' and it has at least these fields
- name
- country
- paid_until
i'd like to get a report which shows the members in all countries in a certain period of time. the result should have the columns country, "members who paid until 2006 or later", "members who paid from 1998 to 2005", and "total (of column 2 and 3)".

I have a solution but it takes a lot of time to process.
What i did was i tried to get all countries from a separate table (used to populate the select type at registration) and built the first column:

next, i selected the members who paid until 2006 or later..and built the next column (another 'while' loop)

next, the members who paid from 1998 to 2005, and last, i added the 2nd and third values. my code looks something like this:

$result = $dao->getCountries(){

foreach($result as $country){

current_members = result of ("SELECT count(name) AS count FROM Members WHERE country = '$country' AND paid_until >= 2006")

past_members = result of( "SELECT count(name) AS count FROM Members WHERE country = '$country' AND paid_until >= 1998 AND paid_until < 2006" )

total = current + past members;

}

}


is there a way where i could get the country, number of current paid members, number of past members, and total at the same time? also, if the 2nd or 3rd field's value is zero, it still should appear in the result.

Thanks a lot!

--kyle

Comments

  • hessodreamyhessodreamy Member Posts: 2
    For any summing-up queries, you'll want to become familiar with GROUP BY.
    For something like this, where you want some members counted in one column and some in another column, I usually use a condition within a SUM statement, eg. sum(if(paid_until>=2006,1,0)) as current
    So if the condition is true, 1 is added to the sum, otherwise 0 is added.
    Note: I'm not sure if this is the most efficient way of doing it, but it works.

    So you can use something like this:

    SELECT
    country,
    sum(if(paid_until>=2006,1,0)) as current,
    sum(if(paid_until between 1998 and 2005,1,0)) as past,
    count(*) as total
    FROM members
    GROUP BY country

    Though the count(*) will give you a count of all members for that country, not just the ones in those date range. So instead you could do a similar sum() including all the date ranges:

    sum(if(paid_until>=2006 OR paid_until between 1998 and 2005,1,0)) as total

    or, more elegant would be to use a subquery, if you're using a version later than 4.1:

    SELECT mysubquery.*, current+past as total FROM
    (
    SELECT
    country,
    sum(if(paid_until>=2006,1,0)) as current,
    sum(if(paid_until between 1998 and 2005,1,0)) as past
    FROM members
    GROUP BY country
    ) as mysubquery

    By the way, when posting on an sql forum, you might want to avoid using dynamic script (asp, php...) as people reading here will be from a variety of scripting backgrounds, or maybe none at all. And you don't want to put them off reading your post. Or maybe I'm not giveing them enough credit.
Sign In or Register to comment.