Vic Devic - 6 months ago 17

SQL Question

I want to find in

`Dx1, Dx2, Dx3`

`ID Name`

1 A

2 B

`ID Dx 1 Dx 2 Dx 3`

1 1 1 1

2 1 1 2

`Category_name Count_dx*`

A 5

B 1

Thanks.

Answer

I am giving the answer although your question seems implicit.

```
SELECT
Categories.Name,
COUNT(*) AS Count_dx
FROM Categories
INNER JOIN
(
SELECT
Dx1 dx_val
FROM Dx
UNION ALL
SELECT
Dx2 dx_val
FROM Dx
UNION ALL
SELECT
Dx3 dx_val
FROM Dx
) AS t
ON Categories.ID = t.dx_val
GROUP BY t.dx_val;
```

Probably you want to get the **count of category IDs** (for each category) across the three columns (`Dx1, Dx2 and Dx3`

) in `Dx table`

. If so then the above query does the job.