This article will give you some of the most common queries we see. They are in generic query syntax, so you may need to fill in the portions such as report, column, and date_column with the titles and information from your data.

If you have suggestions for this article, please chat in on the site!

Select All Columns

SELECT * FROM report

Modify Date Format 

For example, changing the date format to one of these: yyyy-mm-dd, mm-dd, or mm-dd-yyyy.

yyyy-mm-dd

SELECT strftime('%Y-%m-%d', `date column`)
FROM report 

mm-dd

SELECT strftime('%m-%d', `date column`)
FROM report 

mm-dd-yyyy

SELECT strftime('%m-%d-%Y', `date column`)
FROM report 

Joins & Grow Metadata Tables

These are often used in the Master Report in Grow.

Union All

SELECT r1.`column1`, r1.`column2`
FROM report1 r1
UNION ALL
SELECT r2.`column1`, r2.`column2`
FROM report2 r2

Left Join

SELECT report1.`column`, report2.`column`
FROM report1
LEFT JOIN report2 ON report1.`column` = report2.`matching_column`

Inner Join

SELECT report1.`column`, report1.`column2`, report1.`column3`
FROM report1
INNER JOIN report2 ON report2.`column` = report1.`matching_column`

Grow Dates Table

SELECT substr(dt.date,1,10) as newDate, report1.`column1`, report1.`column2`, report1.`column3`
FROM _datesTable dt
LEFT JOIN report1 ON `newDate` = report1.`matching_column`

Grow Countries Table

SELECT r1.`column1`, r1.`column2`, ct.`countryName`, ct.`iso2`, ct.`iso3`
FROM _countriesTable ct
INNER JOIN report1 r1 ON ct.`countryName` = r1.`matching_column`

Group by Week/Month

Week

SELECT total (`column_name`) as `Weekly Totals`, strftime('%Y-%m-%d', `date_column`) as Week
FROM report
GROUP BY strftime('%W', `date_column`);

Month

SELECT total(`column_name`) as `Monthly Totals`, strftime('%Y-%m-%d', `date_column`) as `Month`, 
FROM report
GROUP BY strftime('%M', `date_column`);

Rolling Dates 

For example: last 30 days, last 3 months, YTD, same time last year

Last 30 Days

SELECT * 
FROM report
WHERE `date_column` >= date('now', '-30 days')

Last 3 Months

SELECT * FROM report 
WHERE `date_column` >= date('now', '-3 months')

YTD

SELECT * FROM report 
WHERE `date_column` >= date('now', 'start of year')

Same Time Last Year

SELECT * FROM report 
WHERE `date_column` >= date('now', '-1 year', 'start of year') AND `date_column` < date('now', '-1 year')

Sum Column as New Column

SELECT `column_name1`, (SELECT sum(`column_name2`) FROM report) 
FROM report

Order Numerically

Least to Greatest

SELECT * FROM report
ORDER BY `column_name`

Greatest to Least

SELECT * FROM report
ORDER BY `column name` desc

Remove A Row

SELECT * FROM report
WHERE `column_name` != `row_name`

Round to 2 decimal points

SELECT printf("%.2f", `column_name`)
FROM report

Limit The Data Returned

Top Ten

SELECT * FROM report limit 10

Get Number of Weekdays

(SELECT COUNT(date) as cnt FROM _datesTable 
WHERE date >= date('now', 'start of month') AND date <= date('now') AND strftime('%W', date) NOT IN ('0', '6')) dates

Group Last Entry/Entries in a New Column 

For example: current day, last 7 days, last 30 days

Current Day

SELECT `column_name1`, (SELECT total(`column_name2`) FROM report WHERE `date_column` >= date('now')) 
FROM report

WTD

SELECT `column_name1`, (SELECT total(`column_name2`) FROM report WHERE `date_column` >= date('now', 'start of week')) 
FROM report

Last 30 Days

SELECT `column_name1`, (SELECT total(`column_name2`) FROM report WHERE `date_column` >= date('now', '-30 days')) 
FROM report

Return the first day of the week, and grouped it by week, YTD

SELECT date(SUBSTR(date,1,10), '-7 days', 'weekday 0') as date, strftime('%W',substr(date,1,10)) as week
FROM _datesTable
WHERE date >= date('now','start of year')
GROUP BY week 

Example: Nesting The Queries Above

SELECT strftime('%Y-%m-%d', `date_column`), `column_name1`, printf("%.2f", total(`column_name2`)),. . . 
FROM report
WHERE `date_column` > date('now', '-30 days')
ORDER BY `column_name`
GROUP BY `column_name`
limit 10

Questions? Email us at support@grow.com or chat in on the site.

Did this answer your question?