Page History
...
ExcelSQL supports WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET and JOIN clauses.
| Code Block |
|---|
SELECT [NO_HEADER_ROW] [DISTINCT] * | column1 [AS alias1], column2 [AS alias2], ... [FROM worksheet] [WHERE condition] [GROUP BY column1, column2, ...] [HAVING condition] [ORDER BY column1 [DESC|ASC], column2 [DESC|ASC], ...] [LIMIT number [OFFSET number] GO |
...
SELECT clause
The SELECT clause is used to select data from an Excel worksheet.
...
SELECT * | column1 [AS alias1], column2 [AS alias2]
Example
| Code Block |
|---|
SELECT * FROM Sheet1
GO
SELECT city, zipcode FROM zips
GO
SELECT COUNT(*) FROM zips
GO
SELECT SUM(population) AS [Total Population] FROM zips
GO |
...
NO_HEADER_ROW option
NO_HEADER_ROW
By default, the first row is considered as headers. If there is no header, specify the NO_HEADER_ROW option and Excel's headers will be used as column names.
Example
| Code Block |
|---|
SELECT NO_HEADER_ROW * FROM Sheet2 GO SELECT NO_HEADER_ROW COUNT(*) FROM Sheet2 GO SELECT NO_HEADER_ROW B, C FROM Sheet2 GO SELECT NO_HEADER_ROW [employee].A, [project].B AS Project FROM employee INNER JOIN project ON [employee].B = [project].B GO |
DISTINCT clause
DISTINCT
A column can contain duplicate values, and to list the distinct values, use the SELECT DISTINCT clause. The DISTINCT clause can be used to return only distinct values from a set of records.
Example
| Code Block |
|---|
SELECT DISTINCT city FROM zips
GO
SELECT DISTINCT * FROM employee
GO |
...
FROM clause
Determines the specific dataset to examine to retrieve data. For Excel, this would be indicated as a specific worksheet.
...
where the Excel file name is workbook and the worksheet name is worksheet.Back to top
SubTable feature
In addition, to query a range of cells in the worksheet, use the following syntax to specify the starting cell and the ending cell of the range.
...
Note: You must enclose the table and range in quote identifiers [ ] or " ".
Examples
| Code Block |
|---|
SELECT EID, fname FROM [employee$A1:F10] WHERE EID = 10001 AND Fname = 'Jon' GO SELECT COUNT(EID) FROM [Worksheet1$A1:F500] HAVING COUNT(EID) > 5 GO SELECT * FROM [Worksheet1$A1:F10] LEFT JOIN [Worksheet2$A1:F10] ON [Worksheet1$A1:F10].[PID]=[Worksheet2$A1:F10].[EID] GO SELECT NO_HEADER_ROW * FROM "employee$A1:F10" LIMIT 100 OFFSET 5 GO |
You can also use the LIMIT and OFFSET clauses to query a range of cells in the worksheet.
SELECT * FROM worksheet LIMIT 500 OFFSET 10
Examples
| Code Block |
|---|
SELECT NO_HEADER_ROW B, C, D FROM orders LIMIT 1000 OFFSET 5
GO |
...
WHERE clause
The WHERE clause, sometimes called the predicate, states the qualifying conditions for a query. You can combine the ExcelSQL Arithmetic Operators and the ExcelSQL Comparison Operators in the WHERE clause. Multiple conditions can be joined by the AND and OR clauses, optionally surrounded by (parentheses) to group them. Only the records that satisfy the specified criteria are returned by the query.
...
condition1 AND condition2 OR condition3 AND condition4
is equivalent to
(condition1) AND (condition2 OR condition3) AND (condition4)
Examples
| Code Block |
|---|
SELECT * FROM Sheet1 WHERE "col1" = 'USD' AND "col2" = 'INR'
GO
SELECT city, zipcode FROM zips WHERE state LIKE '%MA%'
GO
SELECT COUNT(*) FROM zips WHERE state IN ('MA','PA')
GO
SELECT COUNT(*) FROM zips WHERE country = 'Germany' OR city='Berlin'
GO
SELECT * FROM Project WHERE EXISTS (SELECT * FROM Employee)
GO
SELECT * FROM Employee WHERE E_id NOT BETWEEN 1 AND 2
GO
SELECT * FROM Employee WHERE Joining_date = '2014-6-28 10:50:53'
GO |
...
Notes
- Timestamp values will be displayed using the format specified in the locale. When you specify the criteria in the WHERE clause, you need to use the JDBC timestamp format. If you want to specify the timestamp criteria using a different format, you can use the PARSEDATETIME function.
Example
| Code Block |
|---|
SELECT * FROM sheet1
WHERE P_date_time = PARSEDATETIME('6/28/2014 10:50:53 AM', 'MM/dd/yyyy HH:mm:ss a') |
...
- WHERE clause cannot be used on a column with mixed data types. An error message "Data conversion error converting" will be displayed.
If a column contains data with mixed data types, the data needs to be converted to one data type. Use the data conversion functions in the query.
Example
| Code Block |
|---|
SELECT * FROM mixedDataTypes.Sheet1
WHERE TO_CHAR(column2) = '11.0'
GO
SELECT * FROM mixedDataTypes.Sheet1
WHERE TO_CHAR(column2) = '11.0' OR TO_CHAR(column2) = 'test'
GO |
...
GROUP BY clause
The GROUP BY clause is used in combination with aggregate functions to group the results by one or more columns. See the SQL Functions page for a list of aggregate functions supported by ExcelSQL.
SELECT column1, aggregate_function(column2) FROM worksheet
GROUP BY column1
Example
| Code Block |
|---|
SELECT name, COUNT(orders) FROM zips GROUP BY name
GO |
...
HAVING clause
The HAVING clause states the qualifying conditions for aggregated values. It is used in conjunction with aggregate functions to filter aggregated values.
SELECT column1, aggregate_function(column2) FROM worksheet
GROUP BY column1
HAVING aggregate_function(column2) operator value
Example
| Code Block |
|---|
SELECT name, COUNT(orders) FROM zips GROUP BY name HAVING COUNT(orders) > 25
GO |
...
ORDER BY clause
ORDER BY is used to sort the results by one or more columns and sorts in ascending order by default. To sort in descending order, use the DESC keyword.
...