Get SUM from COUNT?!?!

Databases
1

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

Here is what I got:

$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 :slight_smile:

2

SUM(u.count)

?

3

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

In MySQL it doesn’t :slight_smile: 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. :shifty:

5

Ugh - it’s an annoying error, but it still saves confusion later on…

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…