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 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

By andreasml - July 8, 2019

Hi Dave

Many many thanks.

It does work

AndreasĀ