# 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](https://wiki.tsnocode.com/index.php?title=Dashboard_diagram_widget_configuration "Dashboard diagram widget configuration").

#### <span class="mw-headline" id="bkmrk-sample%3A-limited-stat-1">Sample: Limited status distribution</span>

The following example displays quite well in a pie chart.

```mysql
 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
```

#### <span id="bkmrk-"></span><span class="mw-headline" id="bkmrk-special-keywords-%2F-v-1">Special keywords / variables</span>

- %UserID%
- %ExclusiveGroupID%
- %GroupListSql%
- %ExclusiveGroupListSql%
- %AndRecordFilter%

## SQL query examples

<div class="vector-body" id="bkmrk--1"><div id="bkmrk--2"></div><div id="bkmrk--3"></div><div id="bkmrk--4"></div></div>### <span class="mw-headline" id="bkmrk-grouping-1">Grouping</span>

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"

#### <span class="mw-headline" id="bkmrk-status-related-data-1">Status related data</span>

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

#### <span class="mw-headline" id="bkmrk-lookup-related-data-1">Lookup related data</span>

```mysql
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
```

#### <span class="mw-headline" id="bkmrk-grouped-variables-1">Grouped variables</span>

```mysql
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
```

#### <span class="mw-headline" id="bkmrk-population-percentag-1">Population percentages</span>

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

### <span class="mw-headline" id="bkmrk-corner-cases-and-wei-1">Corner cases and weird stuff</span>

#### <span class="mw-headline" id="bkmrk-list-of-birthdays-1">List of birthdays</span>

```mysql
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
```