Union Query Total

  • Hi All,

    I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.

    I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).

    Any ideas???

  • clarmatt73 (3/9/2011)


    Hi All,

    I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.

    I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).

    Any ideas???

    You could set up each UNIONed query (or all three middle bits) as a CTE. Nothing stopping you from referencing a CTE twice in the same query.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Chris,

    Thats great, thanks for the help.

    Matt

  • You're welcome Matt. If you're still unsure, post up the whole query.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris Morris-439714 (3/9/2011)


    clarmatt73 (3/9/2011)


    Hi All,

    I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.

    I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).

    Any ideas???

    You could set up each UNIONed query (or all three middle bits) as a CTE. Nothing stopping you from referencing a CTE twice in the same query.

    Oh... be careful, Chris. It really does depend, in this case. When you reference a CTE twice in the same query, the CTE get's executed twice just as if it were a view. If it's a complicated CTE (or string of CTE's), you're doubling the amount of time and resources the query will take to execute.

    My recommendation on something like this is to build a report staging table and populate it using "Divide'n'Conquer" methods. That way you can do easy and fast things like using @@ROWCOUNT to get the count from the query to be included in the footer.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/9/2011)


    Chris Morris-439714 (3/9/2011)


    clarmatt73 (3/9/2011)


    Hi All,

    I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.

    I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).

    Any ideas???

    You could set up each UNIONed query (or all three middle bits) as a CTE. Nothing stopping you from referencing a CTE twice in the same query.

    Oh... be careful, Chris. It really does depend, in this case. When you reference a CTE twice in the same query, the CTE get's executed twice just as if it were a view. If it's a complicated CTE (or string of CTE's), you're doubling the amount of time and resources the query will take to execute.

    My recommendation on something like this is to build a report staging table and populate it using "Divide'n'Conquer" methods. That way you can do easy and fast things like using @@ROWCOUNT to get the count from the query to be included in the footer.

    Good point, Jeff. It's easy to perceive a CTE as a derived table when they're quite different as you point out. It depends mostly upon two things - the rowcounts and the complexity of the CTE's.

    Here's an alternative approach which I use from time to time:

    SELECT CAST('1 Header' AS VARCHAR(20)) AS Section, ...

    INTO #LocalTemporaryTable

    UNION ALL

    SELECT CAST('2 Middle 1' AS VARCHAR(20)), ...

    UNION ALL

    SELECT CAST('3 Middle 2' AS VARCHAR(20)), ...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • The alternative is good but it's still ok to use the CTE... I just dump it's results into a TempTable if I need to call it more than once so it doesn't have to run more than once.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply