Link to home
Start Free TrialLog in
Avatar of lovettjay
lovettjay

asked on

Sql Count with Select Distinct

Hello,  I am trying to count the number of credentials but I am having a difficult time.  What I would like.

+---members-id----+--------Credentials--------+---Count---+
|           1                  |  CMA                                |        1         |
|           2                  |  CSS                                  |         1        |
|           3                  | CMA, CCS                        |         2        |
|           4                  | CMA, EKG, CPT               |        3         |
|           5                  | CSS, EKG, CPT, PT           |        4         |
+-------------------------+-----------------------------------+---------------+

Values from the DB
members_id int
credentials_code varchar


This is my current SQL:
SELECT DISTINCT  mem1.members_id, 
STUFF(
		(SELECT DISTINCT ', ' + credentials_code
		FROM members AS mem JOIN
		 members_credentials  AS mc ON mc.members_id = mem.members_id JOIN
		 credentials AS c ON c.credentials_id = mc.credentials_id
		WHERE mem.[members_id] = mem1.[members_id]
		FOR XML PATH (''))
          , 1, 1, '')  AS [Credentials], COUNT(credentials_code)  AS Count
		  
FROM members AS mem1 JOIN
 members_status AS ms1 ON ms1.members_status_id = mem1.members_status_id LEFT JOIN
 members_credentials AS mc1 ON mc1.members_id = mem1.members_id  JOIN
 credentials AS c1 ON c1.credentials_id = mc1.credentials_id

GROUP BY mem1.members_id

Open in new window


These are my results


+---members-id----+--------Credentials-----------+----Count---+
|           1                  |  CMA                                |       1            |
|           2                  |  CSS                                  |       2            |
|           3                  | CMA, CCS                        |        2           |
|           4                  | CMA, EKG, CPT               |        3           |
|           5                  | CSS, EKG, CPT, PT          |          1          |
+-----------------------+------------------------------------+------------------+


Some of the counts are correct and others are not.  Any help or explanation is appreciated.
SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lovettjay
lovettjay

ASKER

Thanks for your help.  I ended up using Scott's.  Thanks Pawan for your solution as well.  Kyle thanks for your trouble shooting suggestions.