Skip to main content

SQL Query

An SQL Query may extract almost anything from the database.

This process ignores the security restrictions in the system, so you will have to implement them yourself (see special keywords below).

This widget supports different rendering modes, for a deep dive on the diagrams, click here.

Sample: Limited status distribution

The following example displays quite well in a pie chart.

 SELECT s.Status, count(*)
 FROM data_leads1 as d
 JOIN formstatus as s ON  s.StatusID = d.StatusID 
 WHERE d.StatusID IN (11344,11343,11345)
 GROUP BY 1

Special keywords / variables

  • %UserID%
  • %ExclusiveGroupID%
  • %GroupListSql%
  • %ExclusiveGroupListSql%
  • %AndRecordFilter%

SQL query examples

Grouping

The following queies are suitable for use with Dashboard SQL tables. With minor modification they can also be used with the Field type "SQL: Table query"

SELECT s.Status, COUNT(k.DataID)
FROM data_kuglespil as k
JOIN formstatus as s ON k.StatusID = s.StatusID
GROUP BY 1
SELECT l.`Value`, COUNT(k.DataID)
FROM data_kuglespil as k
JOIN formfieldlookup as l ON k.STRRELSE = l.LookupID
GROUP BY 1
ORDER BY l.SortOrder

Grouped variables

SELECT 'Under 5' as Text, COUNT(k.DataID)
FROM data_kuglespil as k
WHERE k.RESULTAT <= 5

UNION

SELECT 'Middel (6-16)' as Text, COUNT(k.DataID)
FROM data_kuglespil as k 
WHERE k.RESULTAT <= 5 AND k.RESULTAT < 17

UNION

SELECT 'Top performer (17)' as Text, COUNT(k.DataID)
FROM data_kuglespil as k
WHERE k.RESULTAT = 17

Population percentages

SELECT
  AVG(IF(StatusID=35,0,100)) as `% completed`
FROM
  data_tpdocument

Corner cases and weird stuff

List of birthdays

SELECT NAVN as Navn, FDSELSDAG as Fødselsdag, ROUND(DATEDIFF(NOW(),FDSELSDAG) / 365 ) as Alder
FROM data_medarbejder
WHERE DATEDIFF( MAKEDATE(YEAR(NOW()),DAYOFYEAR(FDSELSDAG)), NOW() )  BETWEEN 0 AND 7
Back to top