I have the following code:
<div class="col-sm">
<?php
ColumnChart::create(array(
"title"=>"Operierte Patienten (nach Operationsdatum)",
"dataSource"=>(
$this->src("patient")->query("
SELECT DISTINCT 'Operierte vor 2017' as Patienten, COUNT(*) as Summe
FROM dat_patient p
LEFT OUTER JOIN dat_optherapie op ON op.patID = p.ID
-- LEFT OUTER JOIN dat_initialdaten ini ON ini.patID = p.ID
WHERE op.OP1Datum BETWEEN '1950-01-01' AND '2016-12-31'
AND p.Nachname IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dat_optherapie op2 WHERE op2.patID = p.ID AND op2.revision > op.revision)
-- AND NOT EXISTS (SELECT 1 FROM dat_initialdaten ini2 WHERE ini2.patID = p.ID AND ini2.revision > ini.revision)
AND op.OP1OPVerfahren > 0
AND p.Testzwecke = 0
-- GROUP BY Patienten
-- ORDER BY Summe DESC
UNION ALL
SELECT DISTINCT 'Operierte 2017' as Patienten, COUNT(*) as Summe
FROM dat_patient p
LEFT OUTER JOIN dat_optherapie op ON op.patID = p.ID
-- LEFT OUTER JOIN dat_initialdaten ini ON ini.patID = p.ID
WHERE op.OP1Datum BETWEEN '2017-01-01' AND '2017-12-31'
AND p.Nachname IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dat_optherapie op2 WHERE op2.patID = p.ID AND op2.revision > op.revision)
-- AND NOT EXISTS (SELECT 1 FROM dat_initialdaten ini2 WHERE ini2.patID = p.ID AND ini2.revision > ini.revision)
AND op.OP1OPVerfahren > 0
AND p.Testzwecke = 0
-- GROUP BY Patienten
-- ORDER BY Summe DESC
UNION ALL
SELECT DISTINCT 'Operierte 2018' as Patienten, COUNT(*) as Summe
FROM dat_patient p
LEFT OUTER JOIN dat_optherapie op ON op.patID = p.ID
-- LEFT OUTER JOIN dat_initialdaten ini ON ini.patID = p.ID
WHERE op.OP1Datum BETWEEN '2018-01-01' AND '2018-12-31'
AND p.Nachname IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dat_optherapie op2 WHERE op2.patID = p.ID AND op2.revision > op.revision)
-- AND NOT EXISTS (SELECT 1 FROM dat_initialdaten ini2 WHERE ini2.patID = p.ID AND ini2.revision > ini.revision)
AND p.Testzwecke = 0
-- GROUP BY Patienten
-- ORDER BY Summe DESC
UNION ALL
SELECT DISTINCT 'Operierte 2019' as Patienten, COUNT(*) as Summe
FROM dat_patient p
LEFT OUTER JOIN dat_optherapie op ON op.patID = p.ID
-- LEFT OUTER JOIN dat_initialdaten ini ON ini.patID = p.ID
WHERE op.OP1Datum BETWEEN '2019-01-01' AND '2019-12-31'
AND p.Nachname IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dat_optherapie op2 WHERE op2.patID = p.ID AND op2.revision > op.revision)
-- AND NOT EXISTS (SELECT 1 FROM dat_initialdaten ini2 WHERE ini2.patID = p.ID AND ini2.revision > ini.revision)
AND p.Testzwecke = 0
-- GROUP BY Patienten
-- ORDER BY Summe DESC
UNION ALL
SELECT DISTINCT 'Operierte 2020' as Patienten, COUNT(*) as Summe
FROM dat_patient p
LEFT OUTER JOIN dat_optherapie op ON op.patID = p.ID
-- LEFT OUTER JOIN dat_initialdaten ini ON ini.patID = p.ID
WHERE op.OP1Datum BETWEEN '2020-01-01' AND '2020-12-31'
AND p.Nachname IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dat_optherapie op2 WHERE op2.patID = p.ID AND op2.revision > op.revision)
-- AND NOT EXISTS (SELECT 1 FROM dat_initialdaten ini2 WHERE ini2.patID = p.ID AND ini2.revision > ini.revision)
AND p.Testzwecke = 0
-- GROUP BY Patienten
-- ORDER BY Summe DESC
UNION ALL
SELECT DISTINCT 'Operierte 2021' as Patienten, COUNT(*) as Summe
FROM dat_patient p
LEFT OUTER JOIN dat_optherapie op ON op.patID = p.ID
-- LEFT OUTER JOIN dat_initialdaten ini ON ini.patID = p.ID
WHERE op.OP1Datum BETWEEN '2021-01-01' AND '2021-12-31'
AND p.Nachname IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dat_optherapie op2 WHERE op2.patID = p.ID AND op2.revision > op.revision)
-- AND NOT EXISTS (SELECT 1 FROM dat_initialdaten ini2 WHERE ini2.patID = p.ID AND ini2.revision > ini.revision)
AND p.Testzwecke = 0
-- GROUP BY Patienten
-- ORDER BY Summe DESC
")
),
"options"=>array(
"chartArea"=>array(
left=>50,
right=>10, // set this to adjust the legend with, shift the chartarea to left
width=>'100%' // Adjust the chart Area.
)
),
"colorScheme"=>array(
"orange"
)
));
?>
</div>
How can I realize something like displaying the summed result in the title such as this:
"title"=>"Operierte Patienten (nach Operationsdatum) (n = My_sum_result_here)",