January 24, 2003 at 10:55 am
I did and everything is fine.
the Airamt is numeric field
January 24, 2003 at 10:56 am
I mean airtime is numeric
January 27, 2003 at 5:41 am
Try
select x.Vessel,x.callid,sum(x.total_minutes) as total_minutes
into tempweek
from (select Siteid as Vessel,callid,sum(airtime) as total_minutes from oldbill02 with(index(siteid)) where callwhen >='12/31/2002' and substring(callnbr,5,3)<>'976' and siteid<>' ' group by siteid,callid
UNION
select Siteid as Vessel,callid,sum(airtime) as total_minutes from billable_transactions with(index(siteid)) where callwhen <='01/03/2003' and substring(callnbr,5,3)<>'976' and siteid<>' ' group by siteid,callid) x
group by x.vessel,x.callid
Far away is close at hand in the images of elsewhere.
Anon.
January 27, 2003 at 6:09 am
quote:
Tryselect x.Vessel,x.callid,sum(x.total_minutes) as total_minutes
into tempweek
from (select Siteid as Vessel,callid,sum(airtime) as total_minutes from oldbill02 with(index(siteid)) where callwhen >='12/31/2002' and substring(callnbr,5,3)<>'976' and siteid<>' ' group by siteid,callid
UNION
select Siteid as Vessel,callid,sum(airtime) as total_minutes from billable_transactions with(index(siteid)) where callwhen <='01/03/2003' and substring(callnbr,5,3)<>'976' and siteid<>' ' group by siteid,callid) x
group by x.vessel,x.callid
This is identical to the query posted earlier...which apparently didn't work. I'm having a tough time understanding why a query which groups by vessel and callid is apparently not grouping by vessel and callid if the data is not different in some way. Nelson, have you discovered anything more?
January 27, 2003 at 6:39 am
Sorry Jay, missed that one. The sql is good, so I think you are right about the data and espcially spaces, maybe try to remove them thus
select x.Vessel,x.callid,sum(x.total_minutes) as total_minutes
from (select rtrim(Siteid) as Vessel,rtrim(callid) AS 'callid',sum(airtime) as total_minutes from #a group by siteid,callid
UNION
select rtrim(Siteid) as Vessel,rtrim(callid) AS 'callid',sum(airtime) as total_minutes from #b group by siteid,callid) x
group by x.vessel,x.callid
Far away is close at hand in the images of elsewhere.
Anon.
January 27, 2003 at 7:31 am
two things:
1.
in the statement jpipes sent where you got the syntax error near "group"
you need to alias the virtual table created by the union query see below.
select Siteid as Vessel, Callid, sum(total_minutes) as Total_Minutes from
(
select Siteid as Vessel,callid,
sum(airtime) as total_minutes
from oldbill02 with(index(siteid))
where callwhen >='12/31/2002'
and substring(callnbr,5,3)<>'976'
and siteid <> ' ' group by siteid,callid
UNION
select Siteid as Vessel,callid,
sum(airtime) as total_minutes
from billable_transactions with(index(siteid))
where callwhen <='01/03/2003'
and substring(callnbr,5,3)<>'976'
and siteid <> ' ' group by siteid,callid
) as a
group by siteid,callid
2. I do not understand why you are aggregating three times. Why not just use the union query to create a virtual table with the data required and then do the aggregation.
select Siteid as Vessel, Callid, sum(total_minutes) as Total_Minutes from
(
select Siteid as Vessel,callid,
airtime as total_minutes
from oldbill02 with(index(siteid))
where callwhen >='12/31/2002'
and substring(callnbr,5,3)<>'976'
and siteid <> ' '
UNION
select Siteid as Vessel,callid,
airtime as total_minutes
from billable_transactions with(index(siteid))
where callwhen <='01/03/2003'
and substring(callnbr,5,3)<>'976'
and siteid <> ' '
) as a
group by siteid,callid
January 27, 2003 at 7:37 am
aggrevating aggregation
Nigel Moore
======================
January 27, 2003 at 8:22 am
quote:
1. ...sent where you got the syntax error...
already addressed. please read above...
quote:
2. ...aggregating three times....
A UNION statement does an implicit DISTINCT, so either way it doesn't really matter...
However, we're still back to the same old question...duplicate data or not?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply