Is this possible to query?

Question

I have 4 tables that has link to each other.

• patients

• services

• service_procedures

• patient_service_items

I was wondering if it is possible to query this result:

Here's what I query so far but it didn't show my expected result:

select
CONCAT('patient', a.id) AS `pame`,
CONCAT('Service',c.id) AS `ServiceName`,
d.code,
d.price,
(SELECT SUM(psi.price) AS `Total Amount` FROM `patient_service_items` AS `psi`) AS `Total Amount`,
(SELECT COUNT(s.service) AS `Total Service` FROM `services` AS `s` INNER JOIN `patient_service_items` AS `psi` ON s.id = psi.serviceid WHERE psi.serviceid IN (SELECT serviceid FROM patient_service_items GROUP BY serviceid)) AS `Total Service`,
(SELECT COUNT(sp.code) AS `Total Procedure` FROM `service_procedures` AS `sp`) AS `Total Procedure`
from
patient_services a
INNER JOIN patient_service_items b on a.id = b.psid
INNER JOIN services c on b.serviceid = c.id
INNER JOIN service_procedures d on b.procedureid = d.id

Result of my query:

Any other solutions or alternatives is highly appreciated.

Thank you!

Answer 1

One approach is to use subquery to acheive the required result.

SELECT Concat('patient', psi.psid)      AS `pame`, 
       Concat('Service', psi.serviceid) AS `serviceName`, 
       sp.code, 
       sp.price, 
       `total amount`, 
       `total service`, 
       `total procedure` 
FROM   patient_service_items psi 
       INNER JOIN (SELECT psid, 
                          Count(DISTINCT psi.serviceid)   AS `Total Service`, 
                          Count(DISTINCT psi.procedureid) AS `Total Procedure`, 
                          Sum(sp.price)                   AS `Total Amount` 
                   FROM   patient_service_items psi 
                          INNER JOIN service_procedures sp 
                                  ON psi.procedureid = sp.id 
                                     AND psi.serviceid = sp.serviceid 
                   GROUP  BY psi.psid) tblinner 
               ON psi.psid = tblinner.psid 
       INNER JOIN service_procedures sp 
               ON psi.procedureid = sp.id 
                  AND psi.serviceid = sp.serviceid 

You can refer the data and results over here