Results 1 to 7 of 7

Thread: UNION statement

  1. #1
    Join Date
    Feb 2007
    Posts
    42

    Unanswered: UNION statement

    DB ESE
    i have a query that will compute values using SUM() and it will produce more than one row. on the last row, i want to compute for its average. can someone give me an example on what should i do? just an example.. tnx!

  2. #2
    Join Date
    Feb 2007
    Posts
    42
    i tried this..
    SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
    FROM EMP A, TEMP T
    WHERE A.EID=T.TID
    GROUP BY A.EXPENSE
    UNION
    SELECT AVG(SUM(T.GROSS)-A.EXPENSE) AS INCOME
    FROM EMP A, TEMP T
    WHERE A.EID=T.TID
    GROUP BY A.EXPENSE

    but it didn't work because nested column functions is not allowed. so, how will i fix it?

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    try a nested table expression:

    SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
    FROM EMP A, TEMP T
    WHERE A.EID=T.TID
    GROUP BY A.EXPENSE
    UNION ALL
    SELECT AVG(INCOME) FROM
    ( SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
    FROM EMP A, TEMP T
    WHERE A.EID=T.TID
    GROUP BY A.EXPENSE ) X

    ( btw: UNION without ALL eliminates duplicate rows, which results in a sort -> the average will not appear as the last row )

  4. #4
    Join Date
    Feb 2007
    Posts
    42
    i've tried that but it says...
    An unexpected token "" was found following ")". Expected tokens may
    include: "AS".
    ... that i can't see and understand

  5. #5
    Join Date
    Feb 2007
    Posts
    42
    Quote Originally Posted by umayer
    try a nested table expression:

    SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
    FROM EMP A, TEMP T
    WHERE A.EID=T.TID
    GROUP BY A.EXPENSE
    UNION ALL
    SELECT AVG(INCOME) FROM
    ( SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
    FROM EMP A, TEMP T
    WHERE A.EID=T.TID
    GROUP BY A.EXPENSE ) X

    ( btw: UNION without ALL eliminates duplicate rows, which results in a sort -> the average will not appear as the last row )

    oic.. i never thought X is included! so, may i know what it is for? I mean, its essence to prevent the error i got? tnx a lot!
    Last edited by wishlister; 02-19-07 at 12:45.

  6. #6
    Join Date
    Oct 2006
    Posts
    15

    UNION statement

    Try creating query like this
    select x.* from (
    select '1' as inum,sum(T1.TB_STATS_ROWS_WRITE) as first_write,sum(T1.TB_STATS_ROWS_READ) as first_read,
    T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
    from TB_STATS_HISTORY T1
    where
    month(T1.TB_STAT_TS) = month(current date - 1 month)
    and day(T1.TB_STAT_TS) between 1 and 7
    group by T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
    Union all
    select '2' as inum,sum(T1.TB_STATS_ROWS_WRITE) as first_write,sum(T1.TB_STATS_ROWS_READ) as second_read,
    T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
    from TB_STATS_HISTORY T1
    where
    month(T1.TB_STAT_TS) = month(current date - 1 month)
    and day(T1.TB_STAT_TS) between 1 and 7
    group by T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
    ) as x
    order by x.inum,x.TB_STAT_DBNAME";

    I hope this helps

    I created the temporary table and dumped the values in the temporary table and sorted the values.

    Regards
    Rajesh Krishnan

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by wishlister
    oic.. i never thought X is included! so, may i know what it is for? I mean, its essence to prevent the error i got? tnx a lot!

    this is for syntax reasons only.
    It doesn't matter what you code here ( you may write X or HUGO or THISISALONGNAME or whatever you want ), but you have to code at least one character.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •