CODEWITHSUNDEEP
phpmysql
Vic Devic
Vic Devic

Reputation: 17

Count across multiple columns

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.

Upvotes: 2

Views: 58

Answers (1)

1000111
1000111

Reputation: 13519

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.

Upvotes: 1

Related Questions