SQL Syntax Help

[allyne]

I have a query that looks like this (this is part of a large query)
SELECT
Carrier = dbAdmin.dbo.tblCarrierNames.Carrier,
YTD =
SUM(CASE
WHEN Left(InfoROAM2.dbo.cycle_summary.SETTLEMENT_CYCLE,4)= '2001'
THEN InfoROAM2.dbo.cycle_summary.AIR_TIME
ELSE 0
END)
FROM InfoROAM2.dbo.cycle_summary INNER JOIN
dbAdmin.dbo.tblCarrierNames ON
InfoROAM2.dbo.cycle_summary.ROAMING_COMPANY = dbAdmin.dbo.tblCarrierNames.CarrierId INNER JOIN
FinancialReporting.dbo.Top3Current3060TEMP1 ON InfoROAM2.dbo.cycle_summary.SETTLEMENT_CYCLE =
FinancialReporting.dbo.Top3Current3060TEMP1.SETTLEMENT_CYCLE
WHERE InfoROAM2.dbo.cycle_summary.IN_OUT_COLLECT = 'O'
GROUP BY dbAdmin.dbo.tblCarrierNames.Carrier

The Results is :

CARRIER YTD
AT&T Prepaid Services 30,000
AT&T Wireless Services 10,000
ACE 5,000

What I want to do is have the result look like this
AT&T Wireless Services 40,000 (30,000+10,000)
ACE 5,000

It looks like I need a case statement that looks something like this (this is not correct)

Case
WHEN dbAdmin.dbo.tblCarrierNames.Carrier = 'AT&T Prepaid Services'
THEN dbAdmin.dbo.tblCarrierNames.Carrier = 'AT&T Wireless Services'
END

Then I want to be able to do
YTD =
SUM(CASE
WHEN Left(InfoROAM2.dbo.cycle_summary.SETTLEMENT_CYCLE,4)= '2001'
THEN InfoROAM2.dbo.cycle_summary.AIR_TIME
ELSE 0
END)

I know this is incorrect. Can someone tell me what the correct Syntax is?

Thanks for your help!
Cathy

 

[tlbroadbent]

The query works properly. It returns two lines because "AT&T Prepaid Services" and "AT&T Wireless Services" are two different strings. Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris

 

[allyne]

What I want to do is Combine AT&T Prepaid and AT&T Wireless Services together into One String so that it looks like this.

AT&T Wireless Services 40,000

Not:

AT&T Prepaid 30,000
AT&T Wireless Services 10,000

Is there a way to do this?

 

[tlbroadbent]

I could be lots more helpful if I read posts more carefully. Sorry I didn't catch all of your question and the see the CASE statement.

Here is the syntax for the CASE statement.

SELECT
Carrier = Case dbAdmin.dbo.tblCarrierNames.Carrier
When 'AT&T Prepaid' Then AT&T Wireless Services
Else dbAdmin.dbo.tblCarrierNames.Carrier
End
.
.
.
Group By Case dbAdmin.dbo.tblCarrierNames.Carrier
When 'AT&T Prepaid' Then AT&T Wireless Services
Else dbAdmin.dbo.tblCarrierNames.Carrier
End

The remainder of the query between the pieces shown should remain the same. Sorry I didn't get it the first time. Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris

 

[allyne]

Thanks for all your help! I'll give it a try. I think I was making this alot harder than it should have been.

 

[allyne]

This works Great except what I'm now getting is

ATT Wireless Service 10,000
ATT Wireless Service 30,000

Is there any way of getting it to look like this:

ATT Wireles Service 40,000

Thanks Again for all your help!

 

[tlbroadbent]

Sorry. I forgot the single quotes around 'AT&T Wireless Services'.

SELECT
Carrier = Case dbAdmin.dbo.tblCarrierNames.Carrier
When 'AT&T Prepaid' Then 'AT&T Wireless Services'
Else dbAdmin.dbo.tblCarrierNames.Carrier
End
.
.
.
Group By Case dbAdmin.dbo.tblCarrierNames.Carrier
When 'AT&T Prepaid' Then 'AT&T Wireless Services'
Else dbAdmin.dbo.tblCarrierNames.Carrier
End

Add them to your query. Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris

 

[allyne]

Thanks! It worked! I should have caught that myself.