A simple custom Query like a for quick summary. Great for use in Advanced Reports.

I'd like to share a MySQL query structure that I frequently use during my investigations in SugarCRM. The core concept of this query is similar to a summation report, offering a simple yet powerful way to analyze data.

Even if your instance is hosted in such a way that you don't have direct database access, you can still leverage this query structure in the "Custom Query" section of Advanced Reports within the application.

Using a query like this in Advanced Reports can help you independently answer common questions that might otherwise require support assistance. While support is always just a phone call or note away and eager to assist, having the ability to self-serve can save you valuable time and keep your project moving forward more efficiently.

Here’s the basic structure of the query:

SELECT -filter_field- a, COUNT(id) b 
FROM -module_name- 
WHERE -conditions- 
GROUP BY a 
ORDER BY b DESC;

Practical Use Cases:

1. Breaking Down Tracker Records by Module:

   If you want to know how many records in the tracker table are attributable to each module—perhaps to analyze how many actions are linked to automations such as schedulers, BPM, or custom integrations—you can run:

   SELECT module_name a, COUNT(id) b 
   FROM tracker 
   GROUP BY a 
   ORDER BY b DESC;

   
   

2. Analyzing Which Process Definitions Are Triggered Most:

   If you're trying to identify which process definitions have been triggered most often in the past 30 days—perhaps because you suspect some processes are running unnecessarily and you want to optimize them—this query would help:

   SELECT pro_id a, COUNT(id) b 
   FROM pmse_inbox 
   WHERE date_entered >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
   GROUP BY a 
   ORDER BY b DESC;

   
   
   

3. Reviewing Case Status Changes:

   If your goal is to identify cases that have changed status most frequently in the past 90 days—perhaps to coach service agents on improving efficiency by minimizing back-and-forth interactions—you can use:

   SELECT parent_id a, COUNT(id) b 
   FROM cases_audit 
   WHERE field_name = 'status' 
   AND date_created >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
   GROUP BY a 
   ORDER BY b DESC;

By incorporating these queries into your Advanced Reports, you can streamline your investigations and get actionable insights without needing to wait for support. This empowers you to make data-driven decisions more quickly, whether it's for troubleshooting, process optimization, or team coaching.