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:
In the left-hand panel, click Datasets and select the dataset you need. If you do not have a dataset, create one.
In the top-left corner, select the Sources tab.
In the left part of the screen, under Connection, click Add.
Note
The Add button is only available if the source is a database.
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.ShopIDWHERE toYear(t1.OrderDatetime)='2019' -- condition for selecting sales for the specified year (2019)GROUP BY "Store", "Product type" -- group by Store and Product typeORDER BY "Store", "Product Type" -- sort by Store and Product type