How can i get a specific data from my dataset using SQL and PHPmyadmin?-CodePudding

I'm new to sql. I have 3 datasets , patient (columns : id age Zip_Code, size, weight, sex) blood_tests (columns : test_ID, test_date, blood_sugar, laboratory_ID, patient_ID) laboratory (columns : id, name, Zip_code, departments)

how can i get the number of patients per center ? i did this code but it doesnt give the number per

select DISTINCT patient_ID, laboratory_ID from patient,blood_tests where patient.id = blood_tests.patient_ID AND blood_tests.laboratory_ID = laboratory.id;

but i don't know how to get the total number of patients per center, because some of them did more than one exam in the same center and they have done tests in many labs?

For the second question. he aks us to get the 4 tests that a specific patient carried out in a laboratory called 'NWB'. and i did this and noticed that he is patient with and ID = 25 but how can i get that without specifying that the id is 25.

select patient_ID, laboratory.name from patient, blood_tests,laboratory where patient_ID = blood_tests.patient_ID AND blood_tests.laboratory_ID = laboratory.id HAVING laboratory.name = "NWB";

Thank You in advance.

CodePudding user response：

Assuming you're using Mysql with PhpMyAdmin (from now i'll call it PMA) You get the COUNT of patients for each laboratory using GROUP BY keyword. You can read the (unofficial) documentation here. Using GROUP BY you can get the count of tests for each laboratory with SELECT laboratory_ID, COUNT(*) As num_tests FROM blood_tests WHERE 1 GROUP BY laboratory_ID; So the query returns 2 columns one for the laboratory ID and one for the number of tests for that one.

You can see the zip code of laboratory using JOINS statements, in this case you should use INNER JOIN click here for (unofficial) documentation

CodePudding user response：

This query will give you the number of distinct patients that had a blood test done for each laboratory. The query GROUPs all the records with the same laboratory_ID and then counts the number of DISTINCT patients per GROUP. Distinct in this case means that each patient is only counted once per GROUP even if there are multiple records with the same patient_ID and laboratory_ID. However the patient can still be counted in other GROUPs.

SELECT laboratory_ID, COUNT(DISTINCT patient_ID) 
FROM blood_tests
GROUP BY laboratory_ID;

You could join the laboratory table to show the name of the laboratory instead of the id.

SELECT laboratory.nom, COUNT(DISTINCT blood_tests.patient_ID) 
FROM blood_tests
JOIN laboratory ON laboratory.id = blood_tests.laboratory_ID
GROUP BY blood_tests.laboratory_ID;

Your other question is confusing because there is no 'the' test for a patient having carried out 4 tests. There would be 4 tests - which one of them is 'the' test.

Here is a query that will list the ids of all patients that have had 4 test carried out at the laboratory with the id 42.

SELECT patient_ID
FROM blood_tests
WHERE laboratory_ID = 42
GROUP BY patient_ID
HAVING COUNT(patient_ID) = 4;

CodePudding user response：

You need to use COUNT(DISTINCT ~ ) and GROUP BY. We don't need to use the table patients because we have patient_ID in blood_tests.

SELECT
   COUNT( DISTINCT bt.patient_ID) number_patients ,
   bt.laboratory_ID
FROM
    blood_tests bt
JOIN
    laboratory l
ON bt.laboratory_ID = l.id
GROUP BY 
    laboratory_ID;

For the second request:

SELECT
   bt.test_ID,
   bt.patient_ID ,
   bt.laboratory_ID,
   l.name lab_name,
   l.Zip_code, 
   l.departments
FROM
   blood_tests bt
JOIN
   laboratory l
ON 
   bt.laboratory_ID = l.id
WHERE
   l.name = 'NWB'
AND
   (SELECT COUNT(bt.test_ID) 
    FROM blood_tests bb
    WHERE bt.laboratory_ID = bb.laboratory_ID
    AND bt.patient_ID = bb.patient_ID 
    ) = 4 ;