How can I group these codes and total them?

Question

Post reply

How can I group these codes and total them?

Christine M

Old Hand

Points: 393
More actions
November 15, 2007 at 12:33 pm

#75816

Here is my problem. I have written the query below (one of many attempts) and need to improve it so that like codes are totalled together. For example, "WCSSHORE", with 8 characters, is the main office, and "WCSSHOREMNCA", 12 characters is one of the branch offices. There are four in this particular group. I need a sum for all four of them together, rather than the four figures that the query below delivers.
There are at least a dozen other groups that will need to do this for as well. I have tried separating them out by string length but haven't gotten the correct result.
select sum(CorporateRevenue07.CommissionableRevenue) as totals--, CorporateRevenue07.Code
from CorporateRevenue07
where CorporateRevenue07.ProcessDate = '6/1/07'
and CorporateRevenue07.CommissionableRevenue > 0
and CorporateRevenue07.Code like 'WCSSHORE%'
AND CorporateRevenue07.RevDate = '1/1/07'
and CorporateRevenue07.Gate = 'WW'
and CorporateRevenue07.RevType = 'TB'
group by CorporateRevenue07.CommissionableRevenue

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

EvilPostIT SSCrazy Points: 2578 More actions · Answer 1

Could you explain a bit more about the data. Ie what data is available and what result you want?

Nuke the site from orbit, its the only way to be sure... :w00t:

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

select sum(CorporateRevenue07.CommissionableRevenue) as totals,

LEFT(CorporateRevenue07.Code,8) AS Code

from CorporateRevenue07

where CorporateRevenue07.ProcessDate = '6/1/07'

and CorporateRevenue07.CommissionableRevenue > 0

and CorporateRevenue07.Code like 'WCSSHORE%'

AND CorporateRevenue07.RevDate = '1/1/07'

and CorporateRevenue07.Gate = 'WW'

and CorporateRevenue07.RevType = 'TB'

group by LEFT(CorporateRevenue07.Code,8)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Or, better yet... best of both worlds