Report builder: count in two different columns
I have created a report on a database of patients who undergo a type of surgical procedure. I want to produce a list of the total number of procedures as well as another list that contains the number of some specific surgical procedures for every months. I have created the following code:
SELECT DATE_FORMAT(date_of_treatment, '%M %Y') as Date, COUNT(*) as 'Number of aneurysms', ( SELECT COUNT(*) FROM <?php echo $TABLE_PREFIX ?>aortic_aneurysm_disease WHERE type_of_treatment='Open repair' ) as 'Number of open repairs' FROM <?php echo $TABLE_PREFIX ?>aortic_aneurysm_disease GROUP BY Date ORDER BY YEAR(date_of_treatment) DESC, MONTH(date_of_treatment) DESC
The first column contains the months, the second one the number of all cases for each month, but the third column contains the same total number of this specific surgical procedure ('Open repair') in all months, as seen below.
Any help would be more than welcome.
# Date Number of aneurysms Number of open repairs 1 July 2019 4 69 2 June 2019 58 69 3 May 2019 58 69 4 April 2019 35 69 5 March 2019 40 69 6 February 2019 9 69 7 January 2019 12 69