January 24, 2003 at 8:18 am
I'm trying to union 2 tables same structure and I need to add one column.doing this.
select Siteid as Vessel,callid,sum(airtime) as total_minutes into tempweek 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
i'm getting this:
AMSTERDAM Admin 106
AMSTERDAM Admin 285
AMSTERDAM Pax 70
AMSTERDAM Pax 95
what I need is the Amsterdam show the total of admin in one line one and pax also in one line, How do I do that?
thanks
January 24, 2003 at 8:21 am
quote:
I'm trying to union 2 tables same structure and I need to add one column.doing this.select Siteid as Vessel,callid,sum(airtime) as total_minutes into tempweek 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
i'm getting this:
AMSTERDAM Admin 106
AMSTERDAM Admin 285
AMSTERDAM Pax 70
AMSTERDAM Pax 95
what I need is the Amsterdam show the total of admin in one line one and pax also in one line, How do I do that?
thanks
Take out the groupby callid on both statments...
January 24, 2003 at 8:49 am
Doesn't work: this is the error
Column 'oldbill02.callid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
January 24, 2003 at 8:52 am
quote:
Doesn't work: this is the errorColumn 'oldbill02.callid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Sorry, my bad. It is the Vessel field you don't need, as it is repeated in all four lines...Remove it into a WHERE condition...
select callid, sum(airtime) as total_minutes into tempweek from oldbill02 with(index(siteid)) where callwhen >='12/31/2002' and substring(callnbr,5,3)<>'976' and siteid = 'Amsterdam' group by callid
UNION
select 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 = 'Amsterdam' group by callid
🙂
January 24, 2003 at 8:59 am
No my friend, I need the vessel name and the callid for all vessels, not only for Amsterdam
thanks
January 24, 2003 at 9:10 am
Don't know why I didn't see this sooner...
select SiteID, CallID, sum(total_minutes) as "Total_Minutes" from
(
select Siteid as Vessel,callid,sum(airtime) as total_minutes into tempweek 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
)
group by SiteId, callId
January 24, 2003 at 9:31 am
and siteid<>' '
Slight optimization suggestion: Check
Len(RTRIM(SiteId)) > 0
instead. Checking for a length of a string to be 0 vs versus comparing it to an empty string is much faster in CPU cycles. Although slight, every little bit counts!
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 24, 2003 at 9:37 am
something still wrong
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
)
group by siteid,callid
i'm getting this errror:
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'group'.
thanks
January 24, 2003 at 9:38 am
quote:
select Siteid as Vessel,callid,sum(airtime) as total_minutes into tempweek from oldbill02 with(index(siteid)) where callwhen >='12/31/2002' and substring(callnbr,5,3)<>'976' and siteid<>' ' group by siteid,callid
what do you get when you just run the first part?
Nigel Moore
======================
January 24, 2003 at 9:42 am
only the first part works fine
January 24, 2003 at 9:49 am
SELECT * FROM
(select Siteid as Vessel,callid,sum(airtime) as total_minutes into tempweek from oldbill02 with(index(siteid)) where callwhen >='12/31/2002' and substring(callnbr,5,3)<>'976' and siteid<>' ' ) as A
UNION
SELECT * FROM
(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<>' ' ) as B
group by siteid,callid
Edited by - nmoore on 01/24/2003 10:07:46 AM
Nigel Moore
======================
January 24, 2003 at 10:05 am
try this then...
select dt1.Vessel, dt1.Callid, sum(dt1.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 dt1
group by dt1.Vessel,dt1.callid
Edited by - jpipes on 01/24/2003 10:06:00 AM
January 24, 2003 at 10:08 am
nope, i'm still getting this:
AMSTERDAM Admin 106
AMSTERDAM Admin 285
AMSTERDAM Pax 70
AMSTERDAM Pax 95
CAROUSEL Admin 4
CAROUSEL Pax 65
CAROUSEL Pax 166
CROWN ODYSSEY Admin 187
CROWN ODYSSEY Admin 204
I need the Admin sum all together for each vessel, and I need Pax sum all together also
January 24, 2003 at 10:11 am
quote:
nope, i'm still getting this:AMSTERDAM Admin 106
AMSTERDAM Admin 285
AMSTERDAM Pax 70
AMSTERDAM Pax 95
CAROUSEL Admin 4
CAROUSEL Pax 65
CAROUSEL Pax 166
CROWN ODYSSEY Admin 187
CROWN ODYSSEY Admin 204
I need the Admin sum all together for each vessel, and I need Pax sum all together also
Sorry, man, I just don't know, unless there's something in the data itself (like 1 table's callid's have spaces appended...) because the query I sent you groups on the Vessel and CallID. If the results repeat those fields, then there has to be some sort of data discrepancy causing the query processor to view (seemingly) identical rows in different groups...Check into the data itself...
January 24, 2003 at 10:35 am
Can you check the distinct list of values for columns Vessel & CallID this will check that there are no names here with spaces appended to the end.
select distinct vessel from billable_transactions
select distinct callid from billable_transactions
Also what datatype is the airtime column?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply