Remove duplicate before group by SQL

Question

I need remove duplicates in SELECT before count them. I need to count how many test have been done. A patient can't get same value for the same test. I had tried DISTINCT but it would be a GROUP BY(DISTINCT) instead of GROUP BY(GROUP BY). It is pretty much the same but DISTINCT will remove other columns I need in patient_test table. I used a view as a good practice, but I'm not sure that is helping.

TLDR: I want to improve a nested GROUP BY, but I can't find a way.

table: patient_test (12 rows)

Patient | Test      | Value
--------------------------------
Laura   | Test A    |  1.3
Laura   | Test B    |  8.9 <-          
Laura   | Test B    |  8.9 <-  
Laura   | Test C    |  8.9           
Charles | Test B    |  8.9           
Charles | Test B    |  4.6           
Charles | Test C    |  5.6           
Maria   | Test A    |  1.2 <--
Maria   | Test A    |  1.2 <--
Maria   | Test B    |  1.2
Maria   | Test B    |  8.9
Maria   | Test C    |  1.2

I exclude duplicates this way

SELECT * FROM patient_test 
GROUP BY Patient , Test, Value

to get:

table: patient_test (10 rows)

Patient | Test      | Value
--------------------------------
Laura   | Test A    |  1.3
Laura   | Test B    |  8.9         
Laura   | Test C    |  8.9           
Charles | Test B    |  8.9           
Charles | Test B    |  4.6           
Charles | Test C    |  5.6           
Maria   | Test A    |  1.2  
Maria   | Test B    |  1.2
Maria   | Test B    |  8.9
Maria   | Test C    |  1.2

Then I count them this way: (What I want to improve)

SELECT TestName, count(*) AS Result FROM 
(SELECT * FROM patient_test 
GROUP BY Patient , Test, Value) AS tempPatient
GROUP BY tempPatient.TestName;

I'm using a view, but I think that exclude duplicates and them GROUP BY right away could be better.

SELECT TestName, count(*) AS Result FROM 
(CustomView) AS tempPatient
GROUP BY tempPatient.TestName;

I cannot use this one as a part of the solution, because it will remove other columns I need for other procedures.

SELECT DISTINCT Patient,Test,Value
FROM patient_test

This is what I need:

TestName  | Result
----------------------
Test A    |  2
Test B    |  5
Test C    |  3

I think that is not necessary to run GROUP BY twice. I would like to find a way to exclude duplicates using the examples. Any help or suggestion would be nice.

user_10498828 · Answer

Unlike other databases MySql allows more than one columns referenced with DISTINCT inside COUNT():

SELECT Test, COUNT(DISTINCT Patient, Value) AS Result
FROM patient_test 
GROUP BY Test;

See the demo.

user_1467914 · Answer

Seems fine what you have done. I would do something very similar:

SELECT 
    T.Test,
    COUNT(*) AS Result 
FROM 
    (
        SELECT DISTINCT 
            Patient,
            Test,
            Value
        FROM 
            patient_test
        ) T
GROUP BY
    T.Test
;

If you have duplicates in the table, you have to use grouping or distinct to remove them - that is pretty much standard operating procedure in a case like this.

Remove duplicate before group by SQL

2 Answers