asked on

MySQL SELECT GROUP COUNT Query

Hi,

I have a requirement to count 3 different amounts of content in a selected field.

My logic has written a query as follows, but this doesn't work as expected. Please can anyone shed some light?

Thanks,

Marty

SELECT
	A.FriendlyName,
	A.Delegates,
	COUNT(DISTINCT rU.ID_User) AS Registered,
	COUNT(DISTINCT aU.ID_User) AS Accepted,
	COUNT(DISTINCT pU.ID_User) AS Partial
	FROM
	ATL_V2_Affiliates AS A
	Left Join ATL_Users AS rU ON rU.ID_Affiliate AND rU.RegStatus='Registered'
	Left Join ATL_Users AS aU ON aU.ID_Affiliate AND aU.RegStatus='Accepted'
	Left Join ATL_Users AS pU ON pU.ID_Affiliate AND pU.RegStatus='Partial'
	GROUP BY
	A.ID_Affiliate
	ORDER BY
	A.FriendlyName;

Open in new window

Eyal

A.FriendlyName and A.Delegates should be also be included in the group by
and also do right joins

marty_t

ASKER

I added the two fields in the Group By, but that makes no difference.

Using Right Joins instead of Left Joins gives zero rows.

I would like every row from "A" and counts from the others if they contain rows.

Thanks,

Marty

marty_t

ASKER

This works as expected, but I would like to do it via joins instead.

Any ideas?

Thanks,

Marty

SELECT
	A.FriendlyName,
	A.Delegates,
	(SELECT COUNT(rU.ID_User) FROM ATL_Users AS rU WHERE rU.ID_Affiliate=A.ID_Affiliate AND rU.RegStatus='Registered') AS Registered,
	(SELECT COUNT(aU.ID_User) FROM ATL_Users AS aU WHERE aU.ID_Affiliate=A.ID_Affiliate AND aU.RegStatus='Accepted') AS Accepted,
	(SELECT COUNT(pU.ID_User) FROM ATL_Users AS pU WHERE pU.ID_Affiliate=A.ID_Affiliate AND pU.RegStatus='Partial') AS Partial
	FROM
	ATL_V2_Affiliates AS A
	ORDER BY
	A.FriendlyName;

Open in new window

Eyal

this query will have better performance. leave it like this

ASKER CERTIFIED SOLUTION

Eyal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marty_t

ASKER

I cannot believe I let out the =A.ID_Affiliate

Such a schoolboy error, I've been writing these queries for over a decade and couldn't see the wood for the trees.

Many thanks for pointing out my error.

Marty