SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get SUM from COUNT?!?!

    I'm trying to figure out how to get the sum from count within the same sql query?!?!

    Here is what I got:
    PHP Code:
    $query="SELECT c.courseid,c.state,c.coursename,c.country,u.homeclub,u.country,u.count FROM ".$prefix."_courses c 
                INNER JOIN
                (SELECT COUNT(new_userid) count,homeclub,country FROM "
    .$prefix."_users WHERE country='$countryId' GROUP BY homeclub) u
                ON c.courseid=u.homeclub 
                WHERE c.country='
    $countryId' AND c.state='$stateId' ORDER BY c.coursename ASC"
    Any help is appreciated :-)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    SUM(u.count)

    ?

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by jmansa View Post
    I'm trying to figure out how to get the sum from count within the same sql query?!?!

    Here is what I got:
    PHP Code:
    $query="SELECT c.courseid,c.state,c.coursename,c.country,u.homeclub,u.country,u.count FROM ".$prefix."_courses c 
                INNER JOIN
                (SELECT COUNT(new_userid) count,homeclub,country FROM "
    .$prefix."_users WHERE country='$countryId' GROUP BY homeclub) u
                ON c.courseid=u.homeclub 
                WHERE c.country='
    $countryId' AND c.state='$stateId' ORDER BY c.coursename ASC"
    Any help is appreciated :-)
    What exactly are you trying to sum? You are currently pulling a count of new members for each course. Are you trying to sum the total number of new members? If so, it might just be easier to parse the array returned and just count the returned values.

    If you can explain a little further, we might be able to help some more....

    A couple notes:
    • I'm amazed you're not getting an error on your inner join - you're pulling country and homeclub, but only grouping by the homeclub. If you don't group by all the fields on the select, that usually causes and error.
    • If you do try to use the sum as suggested by guido, you'll also need to add a group by clause to the main statement as well.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    I'm amazed you're not getting an error on your inner join - you're pulling country and homeclub, but only grouping by the homeclub. If you don't group by all the fields on the select, that usually causes and error.
    In MySQL it doesn't Yeah, that was a big surprise for me the first time too.

    If you do try to use the sum as suggested by guido, you'll also need to add a group by clause to the main statement as well.
    But if he does all that, it'll still give him the same result, since the count already gives the number of members for each course.

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by guido2004 View Post
    In MySQL it doesn't Yeah, that was a big surprise for me the first time too.
    Ugh - it's an annoying error, but it still saves confusion later on.....

    Quote Originally Posted by guido2004 View Post
    But if he does all that, it'll still give him the same result, since the count already gives the number of members for each course.
    Hence why I asked what he was trying to sum - the baseline query doesn't seem flexible enough to give any different result than he's already getting. If the OP can share what's being sought, then the query can be massaged accordingly...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •