Sum a Union Query

From: news.microsoft.com (noidea_at_nospam.com)
Date: 10/21/04

  • Next message: Hugo Kornelis: "Re: Sum a Union Query" 
    Date: Thu, 21 Oct 2004 15:55:50 +0100

    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

  • Next message: Hugo Kornelis: "Re: Sum a Union Query"

    Relevant Pages

    • Re: Column Totals in a form
      ... I tried the Union query and it tells me "error in FROM Clause" The table ... tblMonthly Report for Wish; Any idea what I am doing wrong? ... "Duane Hookom" wrote: ... totals like: ...
      (microsoft.public.access.modulesdaovba)
    • Re: Sum a Union Query
      ... I have three tables and the union query gives me the following resultset: ... Once I have the above resultset I then want to get the sum of the OrderValue ... > three totals, but the code you post doesn't include any SUM function. ... as based on the sample data you posted. ...
      (microsoft.public.sqlserver.mseq)
    • Re: Column Totals in a form
      ... you can create a union query that combines details with ... totals like: ... "Duane Hookom" wrote: ... each column and have it appear in the Total record/row for each. ...
      (microsoft.public.access.modulesdaovba)
    • Re: column totals
      ... This would require two queries in a UNION query. ... FROM Table1 ... how do I get column totals. ...
      (microsoft.public.access.queries)