Question 63832 in PhpMyAdmin Question posted in
The official documentation can be found here.

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

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.

Tags:

3

Answers


  1. 0

    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
   GROUP_CONCAT(
      bt.test_ID
      SEPARATOR CHAR(10)
      )               test_IDs,
   bt.patient_ID      patientID,
   bt.laboratory_ID.  labo_ID,
   l.name             lab_name,
   l.Zip_code.        lab_zip, 
   l.departments.     lab_dept
FROM
   blood_tests bt
JOIN
   laboratory l
ON 
   bt.laboratory_ID = l.id
WHERE
   l.name = 'NWB'
GROUP BY
   bt.patient_ID ,
   bt.laboratory_ID,
   l.name lab_name,
   l.Zip_code, 
   l.departments
HAVING
   COUNT(bt.test_ID) = 4;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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;

    EDIT based on comment from OP:

    I hope this is useful for anyone trying to learn how to query a database.

    When you query a database your asking it for information based on what is found in the database not based on what you already know to be true. You first need to figure out what it is that you are asking.

    If you say you know that there is some (some means at least one) specific patient that did exactly 4 tests at laboratory NWB, you could ask the database to list all patients that carried out exactly 4 tests at laboratory NWB.

    If you know the specific patient and want to get the tests then you would ask the database for all the tests carried out at at laboratory NWB for this specific patient. In this case the fact that you know there are 4 of them is not a criteria for selection, so it wouldn’t appear in your query.

    If you know there is some patient that did exactly 4 tests and you want to get their tests. What are you asking the database? You could ask: Find me all tests for any patient that has done exactly 4 tests at laboratory NWB. But if there are other patients that also had exactly 4 tests at that laboratory you would get their tests as well.

    Above you saw how to group records together by a criteria and how to count the members of each group.
    The HAVING clause allows you to limit results based on criteria that applies to a group.
    The WHERE clause allows you to limit results based on criteria that applies to a record.

    You should be able to construct a query for any of those scenarios using what was shown above.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search