Mysql Question posted in
The official MySQL documentation can be found here.

How to make query with Group By and Count in MySql?

I have this tables:

L table // teachers

KL org
1 test
2 test1

G table // groups

KG fac
1 maths
2 physics

R table

KR KL KG
1 1 1
2 1 2
3 2 2

And I want to make this query: How many orgs have teachers who give lectures at more than one faculty?

So here is my query, but it is not working as intended:

SELECT COUNT(L.org)
FROM (L INNER JOIN R ON L.KL = R.KL)
    INNER JOIN G ON G.KG = R.KG
GROUP BY G.fac, L.org
HAVING COUNT(G.fac) > 1;

Expected output: 1 (beacause only teacher with Id 1 have more than one faculty, so count of orgs is 1)

Can you help me to figure out what is wrong?

Tags:

2

Answers


  1. 0

    Retrieve the IDs of all teachers who have lectured to more than one faculty using GROUP BY AND HAVING clauses, then use COUNT() function to count the records in this dataset :

    SELECT COUNT(*) 
FROM (
  SELECT L.KL
  FROM L
  INNER JOIN R ON L.KL = R.KL
  INNER JOIN G ON G.KG = R.KG
  GROUP BY L.KL
  HAVING COUNT(*) > 1
) AS S
    Login or Signup to reply.
  2. 0

    Your query is close, but the issue lies in the GROUP BY clause. You’re grouping by both G.fac and L.org, which is causing the count to be aggregated for each combination of organization and faculty. Instead, you should only group by L.org to count the number of organizations with teachers lecturing at more than one faculty.

    SELECT COUNT(L.org)
FROM (L INNER JOIN R ON L.KL = R.KL)
    INNER JOIN G ON G.KG = R.KG
GROUP BY L.org
HAVING COUNT(DISTINCT G.fac) > 1;
    • We’re still joining the L, R, and G tables to get the necessary information about teachers, their lectures, and the faculties.
    • Instead of grouping by G.fac as well, we’re only grouping by L.org.
    • The HAVING clause checks if the count of distinct faculties associated with each organization (COUNT(DISTINCT G.fac)) is greater than 1, indicating that the teacher associated with that organization gives lectures at more than one faculty.

    This query should give you the expected output of 1, which represents the number of organizations with teachers lecturing at more than one faculty.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search