Union and multiple grouping 1

[mttorpy]

I have 3 select statements I am using a UNION to bring them all together, is there a way to get a sum on the entire select statement?

Results are still giving me more than one amount per id.acc(account #)

SELECT a.id_Acc, sum(a.amount) as "Payments"
from t_pv_payment b, t_acc_usage a
where a.id_sess = b.id_sess
and (a.id_usage_interval = '45467')
group by a.id_acc

UNION
SELECT a.id_Acc, sum(a.amount) as "Payments"
from t_pv_accountcredit b, t_acc_usage a
where a.id_sess = b.id_sess
and (a.id_usage_interval = '45467')
group by a.id_acc

UNION
SELECT a.id_Acc, sum(a.amount) as "Payments"
from t_pv_aradjustment b, t_acc_usage a
where a.id_sess = b.id_sess
and (a.id_usage_interval = '45467)
group by a.id_acc
order by a.id_acc

 

[nigelrivett]

select a.id_Acc, sum(a.amount) as "Payments"
from
(
SELECT a.id_Acc, sum(a.amount) as "Payments"
from t_pv_payment b, t_acc_usage a
where a.id_sess = b.id_sess
and (a.id_usage_interval = '45467')
group by a.id_acc

UNION all
SELECT a.id_Acc, sum(a.amount) as "Payments"
from t_pv_accountcredit b, t_acc_usage a
where a.id_sess = b.id_sess
and (a.id_usage_interval = '45467')
group by a.id_acc

UNION all
SELECT a.id_Acc, sum(a.amount) as "Payments"
from t_pv_aradjustment b, t_acc_usage a
where a.id_sess = b.id_sess
and (a.id_usage_interval = '45467)
group by a.id_acc
) a
group by a.id_Acc
order by a.id_acc

note use of union all - otherwise if the amount from the different queries for the same a/c is the same then you would lose one.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mttorpy]

Darn...Still just can not get it to give me any results. seems to have a problem with the group by still

 

[mttorpy]

Great, not problem its working now..THANKS!!