Select All Columns
select * from report
 

Modify Date Format (ex: yyyy-mm-dd, mm-dd, 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

Round to 2 decimal points
select printf("%.2f", `column_name`) from report 

Sum Column as New Column
select `column_name1`, (select sum(`column_name2`) from report) from report 

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`);

Order Numerically (ex: least to greatest, and greatest to least)
Least to Greatest

select * from report order by `column_name` 

Greatest to Least
select * from report order by `column_name` desc 

Limit Returned Data (ex: top ten)
Top Ten

select * from report limit 10 

Rolling Dates (ex: 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') 

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 as Key Value in a New Column (ex: 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 

Remove A Row
select * from report where column_name != `row_name` 

Example of Nesting The Above Queries
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
 

Return the first day of the week, and have it grouped by week, year to date.
 
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 

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

Did this answer your question?