SQL Reference

You can use SQL to create new tables and fields in Sisense .

Below is an example of an SQL query with some SQL main clauses:

SQL Structure SQL Example

SELECT FieldName(s),Function(), *

FROM TableName 1

INNER JOIN\ LEFT JOIN\RIGHT JOIN\FULL JOIN TableName 2

ON TableName 1. JoinField = TableName 2. JoinField

WHERE FieldName Condition

AND\OR FieldName Condition

GROUP BY FieldName(s)

HAVING FieldName(s) Condition

ORDER BY FieldName(s)

SELECT CustomerLastName, CustomerName, ContactEmail, Count(OrderID) AS Counter

FROM Customers c

INNER JOIN Order o

ON c.CustomerID = o.CustomerID

WHERE

o.OrderId BETWEEN 10 AND 100 AND

(c.CustomerName IN ('John','Mary','David') OR c.CustomerLastName LIKE 'Harrison%')

GROUP BY c.CustomerLastName, c.CustomerName, c.ContactEmail

HAVING Count(o.OrderID) > 3

ORDER BY c.CustomerLastName, c.CustomerName

Basic SQL Syntax Guide

SELECT STATEMENT

SELECT SELECT column_name(s)
FROM table_name
SELECT * SELECT *FROM table_name
SELECT DISTINCT SELECT DISTINCT column_name(s)
FROM table_name

Note:

Nested SELECT statements will not work with an AND clause in Sisense.

FUNCTIONS AFTER SELECT CLAUSE

FUNCTIONS

AVG() – Returns the average value

COUNT() – Returns the number of rows

MAX() – Returns the largest value

MIN() – Returns the smallest value

SUM() – Returns the sum

TABLE JOIN FUNCTIONS

INNER JOIN SELECT column_name(s)
FROM table_name1 T1
INNER JOIN table_name2 T2
ON T1.column_name=T2.column_name
LEFT JOIN SELECT column_name(s)
FROM table_name1 T1
LEFT JOIN table_name2 T2
ON T1.column_name=T2.column_name
RIGHT JOIN SELECT column_name(s)
FROM table_name1 T1
RIGHT JOIN table_name2 T2
ON T1.column_name=T2.column_name
FULL JOIN SELECT column_name(s)
FROM table_name1 T1
FULL JOIN table_name2 T2
ON T1.column_name=T2.column_name
AS (alias) SELECT column_name AS column_alias
FROM table_nameor
SELECT column_name
FROM table_name AS table_alias
WHERE SELECT column_name(s)
FROM table_name
WHERE column_name operator value

FUNCTIONS AFTER THE WHERE CLAUSE

AND / OR SELECT column_name(s)
FROM table_name
WHERE condition AND|OR condition
BETWEEN SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
LIKE SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

ORDERING AND GROUPING AFTER WHERE CONDITIONS

GROUP BY SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
ORDER BY SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
HAVING SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

CLAUSES TO COMBINE TABLES

UNION SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

See Also

Basic SQL Guide
MSDN SQL Reference

.r.