Describing a dataset via a source SQL query

Note

To use subqueries as a source, in the connection settings, enable Raw SQL level → Allow subselects in datasets. You can do this when creating or editing the connection.

To add data to a dataset using a SQL query:

  1. Open your dataset. If you don't have a dataset, create a new one.
  2. In the upper left-hand corner, select Sources.
  3. Under Tables, click + Add.
  4. Specify the Source name and enter the SQL code into the Subquery field and click Create.
Example SQL description of a dataset

The following 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 calculated 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
Previous
Create a dataset
Next
Create a field from a data source