SQL query examples

From TempusServa wiki
Jump to navigation Jump to search

Subselect

The following examples are intended for use in 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

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

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