Need help with the best way to write an SQL query

Posted on 2012-09-05
Last Modified: 2012-09-05
Hello Experts,

I have written a fairly complex query (with 11 joins) to report on financial activity.  I ended up writing sub queries to return 'Total Orders'; 'Total Invoices'; 'Total Credit Memos'; and 'Total A/R downpayments'

Now that I have returned those 4 fields (along with many columns of supporting data) I have been asked to do some simple math on the 4 fields listed above.  For example, total the Invoices, credit memos and A/R downpayments and display as a column titled 'Total billed'.

The only way I know how to do this is to cut and paste the sub query that creates each existing column and add the result of each sub query.  I would need to do the same thing about 5 more times for other fields that have been requested.

It would look something like this:

(SELECT SUM(Invoices) FROM .......)
(SELECT SUM(Credit Memos) FROM ....)
(SELECT SUM(A/R Downpayments) FROM...)
AS 'Total Billed'

Open in new window

This seems very cumbersome and very inefficient.  Bear in mind that each sub query is 5 or 6 lines long.  I experimented with assigning the value returned by a sub query to a variable, and that doesn't seem to work.

Can anybody offer a suggestion of a better way to do what I'm trying to do ?

Thanks !!
Question by:Steve5140
    LVL 10

    Expert Comment

    Not that I've tried it, but if the from / where for each query is the same you should be able to do something like:

    SELECT Sum(Invoice) + Sum(Credit Memos) + Sum(A/R Downpayments) AS TotalBilled FROM ....
    LVL 10

    Expert Comment

    Could you wrap your various chunks of SQL code that calculate the individual figures into a stored procedure, and as each chunk of code is executed to calculate a particular value, write that value into a table?  Once all of the individual values are calculated, you could write an aggregate query as the final part of the stored procedure that adds the values you have stored in the table.

    LVL 13

    Expert Comment

    Like armchair_scouse said, probably your best bet would be to create a stored procedure to implement your logic.
    You'd create a temp table (or table variable) and insert all your initial data (with subquery results) and empty fields (or filed with 0.00) for your calculation fields.
    Once you've done this you can run an update on the temp/variable with the calculations needed.
    At the end you'd do a select over the table to return the processed dataset.
    LVL 2

    Expert Comment

    You could encapsulate each query in a user definded function in order to allow SQL to precompile the query and have a better performance.

    DECLARE @varInvoices INT
    DECLARE @varMemos INT
    DECLARE @varPayments INT

    DECLARE @varTotal INT

    SET @varInvoices =  dbo.MyInvoiceFunction()
    SET @varMemos = dbo.MyCreditMemos()
    SET @varPayments = dbo.MyDownpayments()

    SET @varTotal  = @varSUM1  + @varMemos + @varPayments

    Author Comment

    Thanks for all the input, that is what I love about this board !!

    Couple of comments:

    JEaston - each sub query selects from unique, unrelated tables, so I don't think I can combine the subqueries as you suggest

    LionKing - You fired an idea that I think may be my best option.  The results of the query are bound to a .net data grid.  What I may do is add some empty fields to the query as you suggest and then run down the data grid and fill in the blanks with the required calculations.  This has the added advantage of moving the subquery processing off the SQL server and onto the client machine.

    RansomMule - interesting idea, but as every row is returned by the main query the sub query runs with a new 'Project number' in the WHERE clause.  I don't believe that I can call SET in the main query (maybe I don't understand what you are saying).

    If there are no objections I will award the points to LionKing.
    LVL 13

    Expert Comment

    Remember that calculations will be processed faster by the server than by any client.
    Most of the time, you'll rather have more data processing on the server side in order to take advantage of the server "power".
    LVL 68

    Accepted Solution

    Put the results of the big, initial query into a temp table.  Then query on that temp table to do the additional SUMs/calculations.

    SELECT ...same_as_existing_query...
    INTO #temp_save
    FROM ...same_as_existing_query...

        (SELECT SUM([Orders]) AS [Total Orders] FROM #temp_save) AS [Total_Orders],
        (SELECT SUM([Credit Memos]) AS [Total Credit Memos] FROM #temp_save) AS [Total Credit Memos],
        (SELECT SUM([Orders]) + SUM([Credit Memos]) + SUM([...]) + ... AS [Total Billed]
    FROM #temp_save

    Author Comment


    Per genius !!!  Works like a charm.

    Problem is that I already assigned points before I got your answer...

    What now ?
    LVL 68

    Expert Comment


    You can ask that the q be reopened so you can adjust the point awards.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now