Report builder: count in two different columns

3 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: July 8, 2019   (RSS)

By andreasml - July 8, 2019


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:

      DATE_FORMAT(date_of_treatment,  '%M %Y') as Date,
          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 
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.

Kind regards


#	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

By andreasml - July 8, 2019

Hi Dave

Many many thanks.

It does work