CODEWITHSUNDEEP
mysql
Arvind Kushwaha
Arvind Kushwaha

Reputation: 819

mysql count not working with join and where clause

If I am using this query:

SELECT clinic.id
FROM clinic 
JOIN dentist ON dentist.id = clinic.dentist_id 
JOIN user ON user.id = dentist.user_id 
WHERE user.status = 'Active'
AND clinic.status = 'Approved'
AND user.role_id = 2
GROUP BY clinic.id;

So it gives me all rows like

32
35
36
42
44
47
50

Bug If I am going to get total count of results so I apply query :

SELECT count(clinic.id) AS cnt
FROM clinic 
JOIN dentist ON dentist.id = clinic.dentist_id 
JOIN user ON user.id = dentist.user_id 
WHERE user.status = 'Active'
AND clinic.status = 'Approved'
AND user.role_id = 2
GROUP BY clinic.id;

I just applied count() to id, and it returns

1
1
1
1
1
1
1

It does not returns 7, I want result as 7. Can anybody guide please.

Upvotes: 2

Views: 150

Answers (3)

BigBlackQuery
BigBlackQuery

Reputation: 8

DO NOT USE GROUP BY or try this

SELECT clinicID,SUM(case when clinic !=null then 1 ELSE NULL END).id AS cnt
FROM clinic 
JOIN dentist ON dentist.id = clinic.dentist_id 
JOIN user ON user.id = dentist.user_id 
WHERE user.status = 'Active'
AND clinic.status = 'Approved'
AND user.role_id = 2
GROUP BY clinic.id;

Upvotes: 0

Phil Walton
Phil Walton

Reputation: 963

SELECT count(clinic.id) AS cnt
FROM 
clinic 
JOIN dentist ON dentist.id = clinic.dentist_id 
JOIN user ON user.id = dentist.user_id 
WHERE user.status = 'Active' AND clinic.status = 'Approved' AND user.role_id = 2

Remove the GROUP BY clause.

Upvotes: 2

pala_
pala_

Reputation: 9010

You have two choices that I can see. Since you are grouping, you are getting the count per group, which is expected behaviour. To get the total count, you either need to remove the group by and do this:

SELECT count(distinct clinic.id)
FROM clinic 
  JOIN dentist ON dentist.id = clinic.dentist_id 
  JOIN user ON user.id = dentist.user_id 
WHERE user.status = 'Active' 
 AND clinic.status = 'Approved' 
 AND user.role_id = 2

Or simply use your first query as a subquery in a new query, like this:

select count(*) from (
  SELECT clinic.id
  FROM clinic 
    JOIN dentist ON dentist.id = clinic.dentist_id 
    JOIN user ON user.id = dentist.user_id 
  WHERE user.status = 'Active' 
    AND clinic.status = 'Approved' 
    AND user.role_id = 2  
  GROUP BY clinic.id) q

Upvotes: 2

Related Questions