selection with unit

annamaria · #1 (**permalink**) 08-01-07, 11:18

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)

ARWinner · #2 (**permalink**) 08-01-07, 11:46

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

r937 · #3 (**permalink**) 08-01-07, 11:49

note: should probably be UNION ALL instead of UNION

annamaria · #4 (**permalink**) 08-01-07, 15:56

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)

r937 · #5 (**permalink**) 08-01-07, 16:00

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

stolze · #6 (**permalink**) 08-02-07, 03:52

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.

r937 · #7 (**permalink**) 08-02-07, 04:59

gee, stolze, that looks a lot like post #5

stolze · #8 (**permalink**) 08-03-07, 03:52

Err... right. That happens when you don't read all the answers first. ;-)

Internet Services

Web Development

Digital Marketing

Consumer Tech