Describing a dataset via a source SQL query
Note
To use subqueries as a source, in the connection settings, enable Raw SQL level → Allow subqueries in datasets when creating or editing a connection.
To add data to a dataset using a SQL query:
Warning
If you use a new DataLens object model with workbooks and collections:
- In the left-hand panel, select
- Open the appropriate workbook and select an object.
Follow the guide from step 2.
-
In the left-hand panel, click
-
In the top-left corner, select the Sources tab.
-
In the left part of the screen, under Connection, click
Note
The
-
Enter a Source name and enter the SQL code in the Subquery field.
-
Click Create.
A SQL query joins data from samples.MS_SalesFacts
, samples.MS_Products
, and samples.MS_Shops
and groups them on the Store
and the Product type
fields. A sales total is computed for each group. At the same time, only sales records from 2019 are taken into consideration:
SELECT
t3.ShopName AS "Store",
t2.ProductCategory AS "Product type",
COUNT(t1.OrderID) AS "Number of sales",
SUM(t1.Price*t1.ProductCount) AS "Sales total"
FROM
samples.MS_SalesFacts t1
INNER JOIN samples.MS_Products t2 ON t2.ProductID=t1.ProductID
INNER JOIN samples.MS_Shops t3 ON t3.ShopID=t1.ShopID
WHERE
toYear(t1.OrderDatetime)='2019' -- condition for selecting sales for the specified year (2019)
GROUP BY "Store", "Product type" -- group by Store and Product type
ORDER BY "Store", "Product Type" -- sort by Store and Product type