Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Smile Unanswered: selection with unit

    I have this query:

    select
    code, date, sum(amount)
    from tablemovnew
    group by code,date
    unit
    select
    code, date, sum(amount)
    from tablemovold
    group by code,date

    The result yields two records for each code: one records has the total of the amounts of the first table, and the second record has the total of the amounts of the second table.
    Now I would like to obtain a single record for every code, with the sum of the two sums (of amounts).
    I tried by making a selection in which I nest the afore mentioned query, but
    unsuccessfully.
    What can I do?
    Anna - Verona (Italy)

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Simple, use a common table expression:

    with t1 (code, date, summation) as (
    select
    code, date, sum(amount)
    from tablemovnew
    group by code,date
    UNION
    select
    code, date, sum(amount)
    from tablemovold
    group by code,date
    ) select code,date,sum(summation) from t1 group by code,date

    Andy

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    note: should probably be UNION ALL instead of UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by ARWinner
    Simple, use a common table expression:
    with t1 (code, date, summation) as (
    select
    code, date, sum(amount)
    from tablemovnew
    group by code,date
    UNION
    select
    code, date, sum(amount)
    from tablemovold
    group by code,date
    ) select code,date,sum(summation) from t1 group by code,date
    Andy
    Excuse me (I'm not at the office at the moment and I don't have QMF here at home), shall I write exactly what you indicated? I mean shall I begin the query with 'with t1 (code,.....'
    Thank you.
    Anna - Verona (Italy)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, WITH is the first word in the query

    alternatively (since the CTE occurs only once), you can do this --
    Code:
    select code, date, sum(summation) as overall_sum
      from (
           select code, date, sum(amount) as summation
           from tablemovnew
           group by code, date
           UNION
           select code, date, sum(amount)
           from tablemovold
           group by code, date
           ) as t1
    group 
        by code, date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by annamaria
    I have this query:
    The result yields two records for each code: one records has the total of the amounts of the first table, and the second record has the total of the amounts of the second table.
    Now I would like to obtain a single record for every code, with the sum of the two sums (of amounts).
    I tried by making a selection in which I nest the afore mentioned query, but
    unsuccessfully.
    Andy already gave you one solution. I just would like to add this:

    Everything in SQL are tables. That's the most basic principle.

    The result of your two queries are tables where each table has a single row. Unioning both tables via UNION [ALL] also yields a table. Thus, your question comes down to how to combine/sum values from a column of a table. As you know, you can use the SUM() column function (aka aggregate function) for that.

    Btw, besides CTEs you can also use subselects for that:
    Code:
    SELECT t.code, t.date, SUM(t.amount)
    FROM   ( SELECT code, date, SUM(amount)
             FROM   tablemovnew
             GROUP BY code, date
             UNION ALL
             SELECT code, date, SUM(amount)
             FROM   tablemovold
             GROUP BY code, date ) AS t(code, date, amount)
    GROUP BY t.code, t.date
    You can see that the subselect in the FROM clause is exactly your query. I just wrapped another SELECT statement around it to apply the additional aggregation step.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gee, stolze, that looks a lot like post #5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Err... right. That happens when you don't read all the answers first. ;-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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