SQL Query

General Concepts

SQL Query is installed as part of the Administration module and it requires internet access. SuiteCRM Administrators can access the module. Other users designated by administrators can access it, when they are provided with a role access.

Running SQL Query

To query SugarCRM's MySQL database, administrators enter their SQL statement, as shown in the example below. The Run SQL button executes the query and sets the results for viewing.

Viewing the Resulting Records

Use the page vertical and horizontal scroll bars (below the results table) to view the query results. The View as PDF button shows the results in a PDF format, which users can save to a PDF file.

In cases a query shows no results, check that the SQL statement has a LIMIT or a WHERE clause in order to contain large returned results sets within a reasonable operational size. The suitecrm.log file of your instance will also show details/cause of some unexpected SQL errors.

Charting the Results

The results of queries that summarize data, can be turned into a graphic charts by selecting a chart type and some additional options.

Every chart exposes a number of options that customize its look. The options are expressed as name:value pairs. In the two examples above, the Chart Options used are:

title: "Activities by Month",is3D: true,width: 500,height: 400,seriesType: "bars",hAxis: {title: "Month"},vAxis: {title: "Activity"}' for the ComboChart and title:By Sate,is3D:true for the PieChart

.

Saving the Query and Its Results for Reporting

Queries can be named and then saved and recalled for editing and reuse. The results of the query can be copied or exported to a file for reporting and other processing in a spreadsheet or other applications.

Click on Select to Copy button above the results and then click the right mouse button over the highlighted selection to copy it . Click on Export as CSV button to download the results for use in another application.

Sample Queries for Testing

• SELECT IFNULL(accounts.industry,'') industry, COUNT(*) as hits FROM accounts WHERE coalesce(LENGTH(accounts.industry),0) > 0 GROUP BY industry -- try it with the PieChart type limit 100

• SELECT u.user_name , sum(IFNULL(amount_usdollar,0)) dollar FROM opportunities INNER JOIN users u ON opportunities.assigned_user_id=u.id AND u.deleted=0 WHERE coalesce(LENGTH(sales_stage),0) > 0 and (((opportunities.date_closed >= '2015-01-01' AND opportunities.date_closed <= '2016-12-31'))) group by u.user_name -- try it with the PieChart type limit 100

• SELECT DATE_FORMAT(c.date_start,'%b %y') Month, count(c.id) Calls ,(SELECT count(t.id) from tasks t where DATE_FORMAT(c.date_start,'%b %y')=DATE_FORMAT(t.date_due,'%b %y') ) as Tasks ,(SELECT count(m.id) from meetings m where DATE_FORMAT(c.date_start,'%b %y')=DATE_FORMAT(m.date_start,'%b %y') ) as Meetings ,(SELECT count(e.id) from emails e where DATE_FORMAT(c.date_start,'%b %y')=DATE_FORMAT(e.date_sent,'%b %y') ) as Emails FROM calls c -- WHERE YEAR(c.date_start) = 2016 GROUP BY Month ORDER BY c.date_start -- try it with the ColumnChart type limit 100

• SELECT upper(TABLE_NAME) as MODULE, TABLE_ROWS as RECORDS FROM INFORMATION_SCHEMA.TABLES WHERE table_schema like 'sugar%' -- and TABLE_NAME like 'ACCOUNT%' ORDER BY table_name -- Lists all module/table names for reference in SQLQuery limit 250

• SELECT distinct COLUMN_NAME AS FIELD, case when DATA_TYPE like 'var%' then 'Text' when DATA_TYPE = 'char' then 'Text' when DATA_TYPE like 'tiny%' then 'Boolean' else DATA_TYPE end as TYPE,
  case when COLUMN_DEFAULT is null then 'none' else COLUMN_DEFAULT end as FIELD_DEFAULT, upper(TABLE_NAME) as MODULE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ACCOUNTS' -- < change module name here to list its field names limit 100