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