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

Hi

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.

Kind regards

Andreas

#	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 daniel - July 8, 2019

Hi Andreas,

You should be able to achieve the desired result with this query:

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

You were already very close, the only thing to know is that a subquery (like how you were selecting the "Open repair" records) doesn't "know" anything about the main query around it, so we need to add some context for it to select the correct records. In this case, we've added some matching on the formatted Date column.

Let me know if this fixes your issue, or if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com