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----+------- -Credentia ls-------- +---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:
These are my results
+---members-id----+------- -Credentia ls-------- ---+----Co unt---+
| 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.
+---members-id----+-------
| 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
These are my results
+---members-id----+-------
| 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER