Vic Devic Vic Devic - 6 months ago 17
SQL Question

Count repeated some categories mysql

I want to find in

Dx1, Dx2, Dx3
add the number of times the same category and group repeats the name of the category.

Table Categories:

ID Name
1 A
2 B


Table Dx:

ID Dx 1 Dx 2 Dx 3
1 1 1 1
2 1 1 2


Result query:

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.