The individual entries in my MySQL 5.7 database enable their revision through PHP: Already saved data records sets can, thus, be actualized. For every single actualization, which is nothing else than saving the same data record set n times, a revision number is auto-incrementally set (it starts with 0):
+----+-----------+-----------+-------------------+----------+
| ID | PatientID | SurgeryID | blahblahblah | revision |
+----+-----------+-----------+-------------------+----------+
| 1 | 8883 | 7493 | Appendectomy | 0 |
| 2 | 8883 | 7493 | Appendectomy | 1 |
| 3 | 8883 | 7493 | Lap. Appendectomy | 2 |
+----+-----------+-----------+-------------------+----------+
The data record set which interests me, of course, is the one with the highest number since it is the latest revision:
+----+-----------+-----------+-------------------+----------+
| ID | PatientID | SurgeryID | blahblahblah | revision |
+----+-----------+-----------+-------------------+----------+
| 3 | 8883 | 7493 | Lap. Appendectomy | 2 |
+----+-----------+-----------+-------------------+----------+
(Please see that the blahblahblah terms are replaced by numbers (vide infra), I have just termed them here for you to better see the issue.)
How can I COUNT these filtered data record sets?
Done so far:
The SQL query code to filter the highest revision data record set looks like this:
SELECT DISTINCT p.ID, p.PatientID, op.PatID, op.SurgeryID, op.blahblahblah, op.revision
FROM patient_table p
LEFT OUTER JOIN surgery_table op ON op.PatID = p.PatientID
WHERE some restrictions
AND p.PatientID = op.PatID -- possibly redundant
AND NOT EXISTS (SELECT 1
FROM surgery_table op2
WHERE op2.PatID = p.PatientID AND op2.revision > op.revision
);
This SQL query code works fine and delivers the correct results.
Now I want to COUNT only the various surgery forms performed. My SQL query code so far looks like this:
SELECT COUNT(IF(op.blahblahblah = '0',1,NULL)) 'No Nurgery',
COUNT(IF(op.blahblahblah = '1',1,NULL)) 'Appendectomy',
COUNT(IF(op.blahblahblah = '2',1,NULL)) 'Lap. Appendectomy',
[... lots of surgical procedures listed here ...],
COUNT(IF(op.blahblahblah = '50',1,NULL)) 'Colostomy',
COUNT(IF(op.blahblahblah = '99',1,NULL)) 'Different Surgery'
FROM surgery_table op
WHERE op.SurgeryDate BETWEEN "2000-01-01" AND "2020-12-31"
This delivers, of course, ALL data record sets irrespective of their revision numbers.
How can I concatenate or merge or whatever the first and the second SQL query to COUNT merely the data records with the highest revision number?
Aucun commentaire:
Enregistrer un commentaire