How to Reuse a Sub Query in SQL

Is possible to reuse subqueries?

You can take the aggregations out into a CTE (common table expression):

with minima as (select t.id, t.type, min(value) min_value
                from table2 t
                where t.type in (1,2,3,4)
                group by t.id, t.type)
select a.id, a.name,
       (select min_value from minima where minima.id = subquery.id and minima.type = 1) as column1,
       (select min_value from minima where minima.id = subquery.id and minima.type = 2) as column2,
       (select min_value from minima where minima.id = subquery.id and minima.type = 3) as column3,
       (select min_value from minima where minima.id = subquery.id and minima.type = 4) as column4
from (select distinct id from table2 t where t.type in (1,2,3,4) and t.value between '2010-01-01' and '2010-01-07') as subquery
     left join a on a.id = subquery.id

Whether this is actually any benefit (or even supported) or not depends on your environment and dataset, of course.

Another approach:

select xx.id, a.name, xx.column1, xx.column2, xx.column3, xx.column4
from (
      select id,
             max(case type when 1 then min_value end) as column1,
             max(case type when 2 then min_value end) as column2,
             max(case type when 3 then min_value end) as column3,
             max(case type when 4 then min_value end) as column4
      from (select t.id, t.type, min(value) min_value
            from table2 t
            where t.type in (1,2,3,4)
            group by t.id, t.type) minima
      group by id
) xx left join a on a.id = xx.id
order by 1

How to reuse a sub query in sql?

Use a Common Table Expression (CTE) if you're using SQL Server 2005+:

with cte as (
      select columns
      from result_set
      where condition_common
    )
select columns
from cte  as subset1
      join
      cte as subset2 
         on subset1.somekey = subset2.somekey
where otherconditions

Reuse of a field from a joined table inside a subquery in FROM clause

Recall SQL's logical order of operations that differ from its lexical order (i.e., order in how it is written). Usually the first step in query processing is the FROM clause, then JOIN, ON, WHERE, GROUP BY, etc. and usually ending with ORDER BY and SELECT (ironically one of the last clauses processed though written first).

Technically, your queries do not involve correlated subqueries since there are no inner or outer levels. Specifically, the derived table t3 and base table t4 are at the same level. The query engine evaluates t3 in isolation by itself during FROM clause step. Then, it evaluates JOIN table, t4, in isolation by itself and finally applies the matching ON logic.

Because t4 is not defined in the universe of t3, MS Access via GUI prompts for that parameter value (where MS Access via ODBC will raise an error). To resolve you have to include all necessary data sources in each table scope:

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM
  (SELECT t2.fieldid, SUM(t2.field3) AS fsum
   FROM t2
   INNER JOIN table4 sub_t4
     ON t2.fieldid = sub_t4.fieldid
   WHERE t2.date > sub_t4.date 
   GROUP BY t2.fieldid
  ) t3
LEFT JOIN table4 t4 
  ON t3.fieldid = t4.fieldid

---

Often, too, using layered queries is beneficial in Access and can help with final, compact queries:

t3 query (save below as a query object)

SELECT t2.fieldid, SUM(t2.field3) AS fsum
FROM t2
INNER JOIN table4 sub_t4
   ON t2.fieldid = sub_t4.fieldid
WHERE t2.date > sub_t4.date 
GROUP BY t2.fieldid

Final query (join saved query)

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM my_saved_query t3
LEFT JOIN table4 t4 
   ON t3.fieldid = t4.fieldid

How to reuse a subquery

Subquery factoring (aka CTEs in other database platforms) is what you need, eg:

with dataset as (select datasetid
                 from   Reportingdatasetmembers
                 where  ReportingDatasetID = param_in_ReportingDataSetID)
select ...
from   some_table_1
where  ...
and    datasetid in (select datasetid from dataset)
union all
select ...
from   some_table_2
where  ...
and    datasetid in (select datasetid from dataset);

SQL Server - Reuse the value returned by the subquery without executing the subquery again

This sounds like a lateral join:

SELECT s.[SecurityId], s.[Service], x.[Total], x.[Total] + 100 AS [Summed]
FROM TableName s
OUTER APPLY (
    SELECT count(Id) AS [Total]
    ---- joins and conditions---- 
    WHERE Id = S.[SecurityId]
) x

SQL reuse a subquery 'AS' as a parameter for another subquery

Consider migrating those subqueries into derived tables (i.e., queries in FROM or JOIN clauses instead of SELECT clause). In fact, two of those subqueries can become whole tables: invoices and second users.

SELECT c.id,
       c.firstname,
       c.lastname,
       c.language,
       c.sex,
       c.company,
       c.city,
       c.postal_code,
       c.email,
       c.created_at,
       u.name,
       agg.last_purchase_date,
       i.id AS last_invoice_id,
       u2.name AS sales_advisor
[...]
FROM 
    clients c
JOIN 
    boutiques b ON b.id = c.boutique_id
JOIN 
    users u ON u.boutique_id = b.id
JOIN 
    brands br ON br.id = b.brand_id
JOIN
    (
     SELECT client_id, max(`created_at`) as last_purchase_date
     FROM invoices
     GROUP BY client_id
    ) agg
  ON c.id = agg.client_id
JOIN 
    invoices i ON i.client_id = agg.client_id
               AND i.created_at = agg.last_purchase_date
JOIN 
    users u2 ON u2.id = i.user_id
[...]

How can I reuse the result of a sub-query in SELECT statement

There are different ways to simplify the query. You could use a series of CTEs to pre-compute the results for the different levels of aggregation. But I think that the most efficient and readable option is to use window functions.

All intermediate counts can be computed in a subquery, using COUNT(...) OVER(...) with various PARTITION BY options, as follows :

SELECT
    force,
    ethnicity,
    COUNT(*) OVER(PARTITION BY force, ethnicity) AS cnt,
    COUNT(*) OVER(PARTITION BY force) AS cnt_force,
    COUNT(*) OVER(PARTITION BY ethnicity) AS cnt_ethnicity,
    ROW_NUMBER() OVER(PARTITION BY force, ethnicity) AS rn
FROM crimes

Then the outer query can compute the final results (while filtering on the first record in each force/ethnicity tuple to avoid duplicates).

Query :

SELECT 
    force,
    ethnicity,
    cnt AS num_searches,
    ROUND(cnt / cnt_force::decimal * 100, 4) AS percentage_of_force,
    ROUND(cnt_ethnicity / 303565::decimal * 100, 4) AS national_average,
    ROUND(cnt / cnt_force::decimal * 100, 4) 
        - ROUND(cnt_ethnicity / 303565::decimal * 100, 4) AS difference_from_average
FROM (
    SELECT
        force,
        ethnicity,
        COUNT(*) OVER(PARTITION BY force, ethnicity) AS cnt,
        COUNT(*) OVER(PARTITION BY force) AS cnt_force,
        COUNT(*) OVER(PARTITION BY ethnicity) AS cnt_ethnicity,
        ROW_NUMBER() OVER(PARTITION BY force, ethnicity) AS rn
    FROM crimes
    ) x
WHERE rn = 1
ORDER BY force, ethnicity;

Demo on DB Fiddle :

| force           | ethnicity | num_searches | percentage_of_force | national_average | difference_from_average |
| --------------- | --------- | ------------ | ------------------- | ---------------- | ----------------------- |
| metropolitan    | Black     | 6            | 46.1538             | 0.0020           | 46.1518                 |
| metropolitan    | Undefined | 1            | 7.6923              | 0.0003           | 7.6920                  |
| metropolitan    | White     | 6            | 46.1538             | 0.0043           | 46.1495                 |
| norfolk         | White     | 1            | 100.0000            | 0.0043           | 99.9957                 |
| north-yorkshire | White     | 2            | 100.0000            | 0.0043           | 99.9957                 |
| northumbria     | White     | 1            | 100.0000            | 0.0043           | 99.9957                 |
| west-yorkshire  | White     | 3            | 100.0000            | 0.0043           | 99.9957                 |

---

Related Topics