Subquery

Simple Example

This query returns the sum of all profits and the max profit value from the Superstore dataset where all the profits are greater than zero. Values greater than zero mean that no losses are reflected in the results.

SELECT d.sumProfit, maxProfit
FROM (
   SELECT SUM(Profit) as sumProfit, MAX(Profit) as maxProfit
   FROM (
       SELECT Profit
       FROM "Superstore"
       WHERE Profit > 0
       )
   ) as d;

CRM Analytics first runs the innermost query: SELECT Profit FROM "Superstore" WHERE Profit > 0.

The output from this query becomes the dataset on which the outer query operates.

SELECT SUM(Profit) as sumProfit, MAX(Profit) as maxProfit
FROM (innermost query);

Optionally, you can refer to an inner query by its alias, if you choose to use one. In the outermost query, you can refer to sumProfit as d.sumProfit, as shown in the example, or you can refer to it simply as sumProfit.

SELECT d.sumProfit, maxProfit FROM (nested subqueries as d);

Subqueries with UNION ALL

The UNION ALL operator combines the results of two datasets into one. It doesn't remove any duplicate rows. This example includes UNION ALL after a subquery.

The first part of this example, up to the UNION ALL statement, returns the total profit and the maximum profit values from the Superstore dataset. After the UNION ALL statement, the query returns the total and maximum profit values from the ClearanceSales dataset. Note that ClearanceSales is intended for example purposes only.

SELECT d.sumProfit as total, maxProfit as max_profit
   FROM (
       SELECT SUM(Profit) as sumProfit, MAX(Profit) as maxProfit
       FROM (
           SELECT Profit
           FROM "Superstore"
           WHERE Profit > 0
           )
       ) as d
UNION ALL (
   SELECT SUM("Profit") as total, MAX("Profit") as max_profit
   FROM "ClearanceSales"
   GROUP BY ()
)

You can also include UNION ALL within a subquery. This example finds all the names in the Superstore and Opportunity datasets that contain "an." The outer query then filters for names that have counts over 30 and returns that subset of results.

SELECT Customer_Name as "name", "count"
FROM (
   (SELECT Customer_Name, count(*) as "count"
   FROM "Superstore"
   WHERE Customer_Name LIKE '%an%'
   GROUP BY Customer_Name
) UNION ALL (
   SELECT User_Name as "name", count(*) as "count" FROM "Opportunity" WHERE User_Name LIKE '%an%' GROUP BY User_Name
   )
) WHERE "count" > 30