Re: Sum a Union Query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 21:27:11 +0200

On Thu, 21 Oct 2004 15:55:50 +0100, news.microsoft.com wrote:

>Hi
>
>I have data in 3 tables that I want to add together to get a total figure
>
>So far I have an sp that uses a union query that gives me 3 totals (1 from
>each table)
>
>How can I now add these 3 totals together? is it a subquery of somesorts or
>do I have to make a temp table of the union query results and then do a sum
>on this?
>
>Complete newbie so any help greatly appreciated.
>
>Heres what I have so far:
>
>CREATE PROCEDURE ems_OrderIntake
>@DateFrom smalldatetime,
>@DateTo smalldatetime
>AS
>SELECT TrnDate, SalesOrder, LineValue AS OrderValue, LineType
>FROM dbo.SorAdditions
>WHERE (TrnDate >=@DateFrom AND TrnDate <= @DateTo) AND (LineType = '1')
>UNION
>SELECT TrnDate, SalesOrder, - CancelledValue AS Cancels, LineType
>FROM dbo.SorCancelled
>WHERE (TrnDate >=@DateFrom AND TrnDate <= @DateTo) AND (LineType = '1')
>UNION
>SELECT TrnDate, SalesOrder, ChangeValue, LineType
>FROM dbo.SorChanges
>WHERE (TrnDate >=@DateFrom AND TrnDate <= @DateTo) AND (LineType = '1')
>GO

Hi,

I'm not sure what you mean. You say that your current query gives you
three totals, but the code you post doesn't include any SUM function.

I think I can help you better if you post more detailed information about
your problem. Try it with:

1. The table structure, posted as CREATE TABLE statements. Irrelevant
columns may be omitted, but please do include all defaults, keys and other
constraints. See http://www.aspfaq.com/etiquette.asp?id=5006.

2. Some sample data, in the form of INSERT statements. I don't need
hundreds of rows, just a sample that's large enough to illustrate your
problem. See http://vyaskn.tripod.com/code.htm#inserts.
Do test your CREATE TABLE and INSERT statements against an empty database
before posting them here and fix any errors they might contain.

3. The expected output, as based on the sample data you posted.

4. The query that you have so far (but you don't have to repost it if it's
the same as you posted before), the output this gives you (if an error:
copy and paste the complete error message) and why this is not what you
expected.

5. A concise description of the business problem you're trying to solve.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: how to convert rows to column in Access
    ... Totals: Group By ... Crosstab: Row Heading ... e.g. sample data is like: ...
    (microsoft.public.access.queries)
  • Re: PLEASE HELP - I NEED AN EXPERT
    ... I expect that if you don't provide some sample data and expected output ... against overall totals etc., but cannot Graph as the graph looks at the ... how easy is it to work out in one single query what each person % ...
    (microsoft.public.access.reports)
  • Subtable that automatically expands with totals on the top
    ... I would like to create a little worksheet to do some calculations on ... I would like a section of the worksheet to act like ... a "subtable" with one row for each set of sample data, ... and have all of the totals updated. ...
    (microsoft.public.excel.newusers)