clinica programari

SELECT * FROM (
SELECT
        DATE AS data_raport,
    CONCAT(user.last_name,' ',user.first_name) AS doctor,
    COUNT( * ) AS total_programari,
    (SELECT     COUNT( * ) FROM patient_appointment p WHERE status_id in (4,11,12,14) AND p.`date` = pa.date AND p.doctor_id = pa.doctor_id) AS efectuate,
    (SELECT     COUNT( * ) FROM patient_appointment p WHERE status_id=10 AND p.`date` = pa.date AND p.doctor_id = pa.doctor_id) AS neefectuate,
    (SELECT     COUNT( * ) FROM patient_appointment p WHERE status_id in( 5, 6) AND p.`date` = pa.date AND p.doctor_id = pa.doctor_id) AS anulate,
    (SELECT     COUNT( * ) FROM patient_appointment p WHERE status_id=13 AND p.`date` = pa.date AND p.doctor_id = pa.doctor_id) AS neprezentati,
    (SELECT SUM(payment_value) FROM patient_appointment_payment pap JOIN patient_appointment p ON p.patient_appointment_id = pap.patient_appointment_id WHERE p.date = pa.`date` AND p.doctor_id = pa.doctor_id) AS incasari
FROM patient_appointment pa
LEFT JOIN USER ON user.user_id = pa.doctor_id
WHERE pa.date = '2015-06-18'
GROUP BY doctor_id
ORDER BY doctor asc
) t

UNION

SELECT
    CAST('2015-06-18' AS DATE) as data_raport,
    'Subtotal' as doctor,
    (SELECT COUNT(*) FROM patient_appointment WHERE `date` = '2015-06-18') AS total_programari,
    (SELECT     COUNT( * ) FROM patient_appointment WHERE status_id IN (4, 11, 12, 14) AND `date` = '2015-06-18') AS efectuate,
    (SELECT     COUNT( * ) FROM patient_appointment WHERE status_id IN (10) AND `date` = '2015-06-18') AS neefectuate,
    (SELECT     COUNT( * ) FROM patient_appointment WHERE status_id IN (5, 6) AND `date` = '2015-06-18') AS anulate,
    (SELECT     COUNT( * ) FROM patient_appointment WHERE status_id IN (13) AND `date` = '2015-06-18') AS neprezentati,
    (SELECT SUM(payment_value) FROM patient_appointment_payment pap JOIN patient_appointment p ON p.patient_appointment_id = pap.patient_appointment_id WHERE p.`date` = '2015-06-18') AS incasari

;