SQL - Use results of a query as basis for two other queries in one statement

Question

I'm doing a probability calculation. I have a query to calculate the total number of times an event occurs. From these events, I want to get the number of times a sub-event occurs. The query to get the total events is 25 lines long and I don't want to just copy + paste it twice.

I want to do two things to this query: calculate the number of rows in it, and calculate the number of rows in the result of a query on this query. Right now, the only way I can think of doing that is this (replace @total@ with the complicated query to get all rows, and @conditions@ with the less-complicated conditions that rows, from @total@, must have to match the sub-event):

SELECT (SELECT COUNT(*) FROM (@total@) AS t1 WHERE @conditions@) AS suboccurs, 
       COUNT(*) AS totaloccurs FROM (@total@) as t2

As you notice, @total@ is repeated twice. Is there any way around this? Is there a better way to do what I'm trying to do?

To re-emphasize: @conditions@ does depend on what @total@ returns (it does stuff like t1.foo = bar).

Some final notes: @total@ by itself takes ~250ms. This more complicated query takes ~300ms, so postgres is likely doing some optimization, itself. Still, the query looks terribly ugly with @total@ literally pasted in twice.

Answer 1

SELECT COUNT(*) as totaloccurs, COUNT(@conditions@) as suboccurs
FROM (@total@ as t1)

Answer 2

Put the reused sub-query into a temp table, then select what you need from the temp table.

Answer 3

If your sql supports subquery factoring, then rewriting it using the WITH statement is an option. It allows subqueries to be used more than once. With will create them as either an inline-view or a temporary table in Oracle.

Here is a contrived example.

WITH
x AS
(
    SELECT this
    FROM THERE
    WHERE something is true
),
y AS
(
    SELECT this-other-thing
    FROM somewhereelse
    WHERE something else is true
), 
z AS
(
    select count(*) k
    FROM X
)
SELECT z.k, y.*, x.*
FROM x,y, z
WHERE X.abc = Y.abc

Answer 4

@EvilTeach:

I've not seen the "with" (probably not implemented in Sybase :-(). I like it: does what you need in one chunk then goes away, with even less cruft than temp tables. Cool.