[RESOLVED] returning two COUNT() results in the same MySQL query

[perpetualshaun]

Seems like I've run into something like this before and wasn't able to find a solution, but hoping I can today.

I am trying to write a query that will return some information on the Candidates for a system, information about the matching position, and then the number of References that the Candidate has invited AND the number of those References that have actually completed the survey. Here's a copy of the query that I wrote, and thought would work:

SELECT
			c.*,
			p.position_title, p.position_code,
			COUNT(invited.reference_id) AS invited_count,
			COUNT(completed.reference_id) AS completed_count
		FROM
			rr_candidates c
		LEFT JOIN
			rr_candidate_positions ON pos_candidate_id=c.candidate_id
		LEFT JOIN
			rr_positions p ON rr_candidate_positions.pos_position_id=p.position_id
		LEFT JOIN
			rr_references invited ON invited.reference_position_id=p.position_id
				AND
			invited.reference_candidate_id=c.candidate_id
		LEFT JOIN
			rr_references completed ON completed.reference_position_id=p.position_id
				AND
			completed.reference_candidate_id=c.candidate_id
		LEFT JOIN
			healthcare y ON y.response_pcode=completed.reference_password
				AND
			y.response_client_id=completed.reference_client_id
				AND
			completed.reference_response_ts != ''
				AND
			y.response_completed_on != ''
		WHERE
			(candidate_client_id='3009' OR candidate_client_id='3010' OR candidate_client_id='2530' OR candidate_client_id='3011' OR candidate_client_id='3012')
				AND
			candidate_created BETWEEN 1312171200 AND 1314849599
		GROUP BY
			candidate_id, invited.reference_id

If I change the GROUP BY to just "candidate_id", I get very high numbers in both of the last two columns, the invited_count and completed_count. But then if I change the GROUP BY clause to "GROUP BY candidate_id, invited.reference_id, completed.reference_id" I get 1s all down BOTH columns.

The numbers I'm seeing in the invited_count column with the query above appear to be correct. Just wondering what I can change in my query to see some differences for the surveys that haven't been completed yet.

We're running MySQL 4.1.20 on this server.

Any ideas?

Thanks,
Shaun

[johanafm]

Unless I'm misstaken, the life cycle of version 4.1 ended more than 2 years ago, which means you should most definitely upgrade to 5.5.

In this thread I explain why, when a group by clause is present, every selected field must be part of the group by clause or be part of an aggregate function. This is always the first thing to fix before doing anything else. So, if you really need to select c.*, then you also have to put every single field from c in the group by clause.

Once done with that, you should also ditch one of the two joins on rr_references. Consider two tables, a and b. Table a contains one row which is joined against 4 rows in b.

SELECT a.id, b.id
FROM a
LEFT JOIN b ON a.id = b.a_id

Thus, the above will select 4 rows. How many rows will be selected if you join on b again?

SELECT a.id, b.id
FROM a
LEFT JOIN b ON a.id = b.a_id
LEFT JOIN b AS b_again ON a.id = b_again.a_id

Well, since you now have 4 rows containing a.id (one for each matching row in b) and you know that each such row will match 4 rows in b_again, the answer is 4*4 = 16 rows.

And finally, you can count both taken surveys and those not yet started / completed in one go with one single join on that table. Once again, a little example. Two tables, aa(id, title) and ab(id, a_id, txt), a_id REFERENCES a(id).
aa contains (1, 'music') and ab contains (1, 1, 'Ben Klock'), (2, 1, 'Len Faki'), (3, 1, 'Dinky') and (4, 1, null).

SELECT a.title, COUNT(*)
FROM aa a
LEFT JOIN ab b ON a.id = b.a_id
GROUP BY title

will obviously give you a count of 4 for music. However, you can always group on null / not null or string length 0 / greater than 0 etc.

SELECT a.title, ISNULL(b.txt) AS bnull, COUNT(*)
FROM aa a
LEFT JOIN ab b ON a.id = b.a_id
GROUP BY title, bnull

Since you are now grouping on title (music) and bnull (null or not null), you will get your results split up into a count of 3 (music and not null) and 1 (music and nul). The same thing can be done with string length if that's needed

SELECT a.title, COUNT(*), CHAR_LENGTH(txt) = 0 AS zerolen
FROM aa a
LEFT JOIN ab b ON a.id = b.a_id
GROUP BY title, zerolen