# Quickbooks

### SELECT Statements <a href="#default" id="default"></a>

A SELECT statement can consist of the following basic clauses.

* SELECT
* INTO
* FROM
* JOIN
* WHERE
* GROUP BY
* HAVING
* UNION
* ORDER BY
* LIMIT

### SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:\


| <p>SELECT {</p><p>  [ TOP &#x3C;numeric_literal> | DISTINCT ]</p><p>  {</p><p>    *</p><p>    | {</p><p>        &#x3C;expression> [ [ AS ] &#x3C;column_reference> ]</p><p>        | { &#x3C;table_name> | &#x3C;correlation_name> } .*</p><p>      } [ , ... ]</p><p>  }</p><p>  [ INTO csv:// [ filename= ] &#x3C;file_path> [ ;delimiter=tab ] ]</p><p>  {</p><p>    FROM &#x3C;table_reference> [ [ AS ] &#x3C;identifier> ]</p><p>  } [ , ... ]</p><p>  [ [ </p><p>      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }</p><p>    ] JOIN &#x3C;table_reference> [ ON &#x3C;search_condition> ] [ [ AS ] &#x3C;identifier> ]</p><p>  ] [ ... ]</p><p>  [ WHERE &#x3C;search_condition> ]</p><p>  [ GROUP BY &#x3C;column_reference> [ , ... ]</p><p>  [ HAVING &#x3C;search_condition> ]</p><p>  [ UNION [ ALL ] &#x3C;select_statement> ]</p><p>  [</p><p>    ORDER BY</p><p>    &#x3C;column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]</p><p>  ]</p><p>  [</p><p>    LIMIT &#x3C;expression></p><p>    [</p><p>      { OFFSET | , }</p><p>      &#x3C;expression></p><p>    ]</p><p>  ]</p><p>} | SCOPE_IDENTITY()</p><p> </p><p>&#x3C;expression> ::=</p><p>  | &#x3C;column_reference></p><p>  | @ &#x3C;parameter></p><p>  | ?</p><p>  | COUNT( * | { [ DISTINCT ] &#x3C;expression> } )</p><p>  | { AVG | MAX | MIN | SUM | COUNT } ( &#x3C;expression> )</p><p>  | NULLIF ( &#x3C;expression> , &#x3C;expression> )</p><p>  | COALESCE ( &#x3C;expression> , ... )</p><p>  | CASE &#x3C;expression></p><p>      WHEN { &#x3C;expression> | &#x3C;search_condition> } THEN { &#x3C;expression> | NULL } [ ... ]</p><p>    [ ELSE { &#x3C;expression> | NULL } ]</p><p>    END</p><p>  | &#x3C;literal></p><p>  | &#x3C;sql_function></p><p> </p><p>&#x3C;search_condition> ::=</p><p>  {</p><p>    &#x3C;expression> { = | > | &#x3C; | >= | &#x3C;= | &#x3C;> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ &#x3C;expression> ]</p><p>  } [ { AND | OR } ... ]</p> |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

#### Examples

1.  Return all columns:\


    | `SELECT * FROM Customers` |
    | ------------------------- |
2.  Rename a column:\


    | `SELECT [Name] AS MY_Name FROM Customers` |
    | ----------------------------------------- |
3.  Cast a column's data as a different data type:\


    | `SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Customers` |
    | --------------------------------------------------------------------------- |
4.  Search data:\


    | `SELECT * FROM Customers WHERE Name = 'Cook, Brian'` |
    | ---------------------------------------------------- |
5.  Return the number of items matching the query criteria:\


    | `SELECT COUNT(*) AS MyCount FROM Customers` |
    | ------------------------------------------- |
6.  Return the number of unique items matching the query criteria:\


    | `SELECT COUNT(DISTINCT Name) FROM Customers` |
    | -------------------------------------------- |
7.  Return the unique items matching the query criteria:\


    | `SELECT DISTINCT Name FROM Customers` |
    | ------------------------------------- |
8.  Summarize data:\


    | `SELECT Name, MAX(AnnualRevenue) FROM Customers GROUP BY Name` |
    | -------------------------------------------------------------- |

    See Aggregate Functions below for details.
9.  Retrieve data from multiple tables.\


    | `SELECT i.ReferenceNumber, c.AccountNumber FROM Invoices i INNER JOIN Customers c ON i.CustomerId=c.ID` |
    | ------------------------------------------------------------------------------------------------------- |

    See JOIN Queries below for details.
10. Sort a result set in ascending order:\


    | `SELECT Id, Name FROM Customers  ORDER BY Name ASC` |
    | --------------------------------------------------- |
11. Restrict a result set to the specified number of rows:\


    | `SELECT Id, Name FROM Customers LIMIT 10` |
    | ----------------------------------------- |
12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.\


    | `SELECT * FROM Customers WHERE Name = @param` |
    | --------------------------------------------- |

### Aggregate Functions <a href="#default" id="default"></a>

#### COUNT <a href="#count" id="count"></a>

Returns the number of rows matching the query criteria.\


| `SELECT COUNT(*) FROM Customers WHERE Name = 'Cook, Brian'` |
| ----------------------------------------------------------- |

#### COUNT(DISTINCT) <a href="#countdistinct" id="countdistinct"></a>

Returns the number of distinct, non-null field values matching the query criteria.\


| `SELECT COUNT(DISTINCT Id) AS DistinctValues FROM Customers WHERE Name = 'Cook, Brian'` |
| --------------------------------------------------------------------------------------- |

#### AVG <a href="#avg" id="avg"></a>

Returns the average of the column values.\


| `SELECT Name, AVG(AnnualRevenue) FROM Customers WHERE Name = 'Cook, Brian'`  `GROUP BY Name` |
| -------------------------------------------------------------------------------------------- |

#### MIN <a href="#min" id="min"></a>

Returns the minimum column value.\


| `SELECT MIN(AnnualRevenue), Name FROM Customers WHERE Name = 'Cook, Brian'` `GROUP BY Name` |
| ------------------------------------------------------------------------------------------- |

#### MAX <a href="#max" id="max"></a>

Returns the maximum column value.\


| `SELECT Name, MAX(AnnualRevenue) FROM Customers WHERE Name = 'Cook, Brian'` `GROUP BY Name` |
| ------------------------------------------------------------------------------------------- |

#### SUM <a href="#sum" id="sum"></a>

Returns the total sum of the column values.\


| `SELECT SUM(AnnualRevenue) FROM Customers WHERE Name = 'Cook, Brian'` |
| --------------------------------------------------------------------- |

### JOIN Queries <a href="#default" id="default"></a>

The Provider for QuickBooks supports standard SQL joins like the following examples.

#### Inner Join

An inner join selects only rows from both tables that match the join condition:\


| `SELECT i.ReferenceNumber, c.AccountNumber FROM Invoices i INNER JOIN Customers c ON i.CustomerId=c.ID` |
| ------------------------------------------------------------------------------------------------------- |

#### Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:\


| `SELECT i.ReferenceNumber, c.AccountNumber FROM Invoices i LEFT JOIN Customers c ON i.CustomerId=c.ID` |
| ------------------------------------------------------------------------------------------------------ |

### Date Literal Functions <a href="#default" id="default"></a>

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

#### L\_TODAY() <a href="#ltoday" id="ltoday"></a>

The current day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_TODAY()` |
| ----------------------------------------------------- |

#### L\_YESTERDAY() <a href="#lyesterday" id="lyesterday"></a>

The previous day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()` |
| --------------------------------------------------------- |

#### L\_TOMORROW() <a href="#ltomorrow" id="ltomorrow"></a>

#### The following day. 

| `SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()` |
| -------------------------------------------------------- |

#### L\_LAST\_WEEK() <a href="#llastweek" id="llastweek"></a>

#### Every day in the preceding week. 

| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()` |
| --------------------------------------------------------- |

#### L\_THIS\_WEEK() <a href="#lthisweek" id="lthisweek"></a>

Every day in the current week.\


| `SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()` |
| --------------------------------------------------------- |

#### L\_NEXT\_WEEK() <a href="#lnextweek" id="lnextweek"></a>

Every day in the following week.\


| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()` |
| --------------------------------------------------------- |

Also available:

* L\_LAST/L\_THIS/L\_NEXT MONTH
* L\_LAST/L\_THIS/L\_NEXT QUARTER
* L\_LAST/L\_THIS/L\_NEXT YEAR

#### L\_LAST\_N\_DAYS(n) <a href="#llastndaysn" id="llastndaysn"></a>

The previous n days, excluding the current day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)` |
| ------------------------------------------------------------ |

#### L\_NEXT\_N\_DAYS(n) <a href="#lnextndaysn" id="lnextndaysn"></a>

The following n days, including the current day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)` |
| ------------------------------------------------------------ |

Also available:

* L\_LAST/L\_NEXT\_90\_DAYS

#### L\_LAST\_N\_WEEKS(n) <a href="#llastnweeksn" id="llastnweeksn"></a>

Every day in every week, starting n weeks before current week, and ending in the previous week.\


| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)` |
| ------------------------------------------------------------- |

#### L\_NEXT\_N\_WEEKS(n) <a href="#lnextnweeksn" id="lnextnweeksn"></a>

Every day in every week, starting the following week, and ending n weeks in the future.\


| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)` |
| ------------------------------------------------------------- |

Also available:

* L\_LAST/L\_NEXT\_N\_MONTHS(n)
* L\_LAST/L\_NEXT\_N\_QUARTERS(n)
* L\_LAST/L\_NEXT\_N\_YEARS(n)
