Re: Sum a Union Query
From: Newbie (nospam_at_noidea.com)
Date: 10/24/04
- Previous message: Steve Kass: "Re: & in the where clause"
- In reply to: Hugo Kornelis: "Re: Sum a Union Query"
- Next in thread: Hugo Kornelis: "Re: Sum a Union Query"
- Reply: Hugo Kornelis: "Re: Sum a Union Query"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 24 Oct 2004 16:37:14 +0100
I have three tables and the union query gives me the following resultset:
TrnDate SalesOrder OrderValue LineType
2004-04-12 000678 880.00 1
ditto 000456 -345.00 1
ditto 000103 53.45 1
ditto 00234 12.56 1
from each table there could be numerous records returned for the given date.
Once I have the above resultset I then want to get the sum of the OrderValue
for the union query i.e. the above would return 1 record with one field like
so:
OrderValue
601.01
What I want to know is how to do the sum part on a union query?
Thanks for your help so far
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:iv2gn0hcq8e8r8hbk1lc6bmsm65uk0p440@4ax.com...
> 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)
- Previous message: Steve Kass: "Re: & in the where clause"
- In reply to: Hugo Kornelis: "Re: Sum a Union Query"
- Next in thread: Hugo Kornelis: "Re: Sum a Union Query"
- Reply: Hugo Kornelis: "Re: Sum a Union Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
