[SQL] WITH clause query alias

In SQL you can easily define an alias for a table or column and just as easily for a query or subquery. This can be useful when joining two datasets (SQL ANSI-92 standard):

SELECT *
FROM (SELECT column1, column2
    FROM table1
    [WHERE]) query1
JOIN (SELECT column1, column3
    FROM table2
    [WHERE]) query2
ON query1.column1=query2.column1



but it's especially useful when you need to reuse a query mutliple times. To do so, you can declare it using the WITH clause (SQL-99 standard) before you query:

--query to be reused
WITH myquery AS (
    SELECT column1,..., columnN
    FROM table1 [can even JOIN]
    [WHERE]
)
--your query

SELECT q.column1,
    COUNT(q.column1) as total,
--here we reuse myquery but filter it more deeply

    (SELECT COUNT(q1.column1)
        FROM myquery q1
        WHERE q1.columnN='value'
--this is necessary otherwise the GROUP BY will not work
        AND q1.column1=q.column1) as totalValue
FROM myquery q [can even JOIN]
GROUP BY q.column1
ORDERBY q.column1 ASC;

Here we declared a query myquery producing a dataset with column1,...,columnN columns then selected (by grouping for column1), how many records where present (total) and of those, how many with columnN='value' (totalValue).

Remember that you cannot COUNT(SELECT ...) and that this would have been wrong:


WITH myquery AS (
    SELECT column1,..., columnN
    FROM table1 [can even JOIN]
    [WHERE]
)
SELECT q.column1,
    COUNT(q.column1) as total,
    (SELECT COUNT(q.column1)
        FROM q
        WHERE q.columnN='value') as totalValue
FROM myquery q [can even JOIN]
GROUP BY q.column1
ORDERBY q.column1 ASC;

as you cannot reuse the q alias declared in the outer FROM inside that subquery.