SQL query examples

Subselect field

The following examples are intended for use in "SQL: Subselect" fields. Note refrences to the parent tables are prefixed with ":"

List of files

 SELECT GROUP_CONCAT(FileName SEPARATOR '\n') FROM data_ticket_file WHERE  IsDeleted = 0 AND DataID = :DataID

Grouping

The following queies are suitable for use with Dashboard SQL tables and the Field type "SQL: Table query"

Status related data

 SELECT s.Status, COUNT(k.DataID)
 FROM data_kuglespil as k
 JOIN formstatus as s ON k.StatusID = s.StatusID
 GROUP BY 1;

Lookup related data

 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