Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

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

0
marty_t
Asked:
marty_t
  • 3
  • 3
1 Solution
 
EyalCommented:
A.FriendlyName and A.Delegates should be also be included in the group by
and also do right joins
0
 
marty_tAuthor Commented:
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
0
 
marty_tAuthor Commented:
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

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
EyalCommented:
this query will have better performance. leave it like this
0
 
EyalCommented:
Left Join ATL_Users AS rU ON rU.ID_Affiliate=A.ID_Affiliate AND rU.RegStatus='Registered'
Left Join ATL_Users AS aU ON aU.ID_Affiliate=A.ID_Affiliate AND aU.RegStatus='Accepted'
Left Join ATL_Users AS pU ON pU.ID_Affiliate=A.ID_Affiliate AND pU.RegStatus='Partial'
0
 
marty_tAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now