Grouping on first 6 characters of a column?

Following query, not sure of the syntax to make it happen.
SELECT LOC.GlAccountid
,LOC.PrinAmount
,LOC.IntAmount
,LOC.PaymentDate
,GLA.GLAccount
FROM ac.LOCPayment LOC
INNER JOIN ac.GLAccount GLA on LOC.GLAccountId= GLA.GLAccountId

Open in new window

LVL 8
ApexCoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify the current output, and the requested output, please?
not sure what exactly you need?
0
ApexCoAuthor Commented:
Sorry about that, I just realized I didn't include enough info.

Wanting to group on the GLAccount, the data looks something like this:

2000021001
2000021001
2000021001
2000021001
2000021001
2000025002
2000025002
2000025002
2000025002

I would like to group it on the first 6 numbers of that column. So the result set would look like:

Group 1
200002
200002
200002
200002
200002

Group 2
200004
200004
200004
200004
0
ApexCoAuthor Commented:
I made a typo there on the 2nd group, but I think you will see what I mean.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
to get the first 6 characters:

LEFT(yourcol, 6)

not sure what you mean by "grouping"
in a report?
0
ApexCoAuthor Commented:
Yep, in a report.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not sure how to do that, but I am sure then once you include the above expression in the select, you can easily "group" by that value...
0
ApexCoAuthor Commented:
Oddly enough, if I do this.

LEFT(GLA.GLAccouunt, 6) as Group1

It won't let me group it by Group1. States the column is invalid.
0
imitchieCommented:
Hi ApexCo,

I think you mean "group" as in "sorted within"? Try this

SELECT LOC.GlAccountid
,LOC.PrinAmount
,LOC.IntAmount
,LOC.PaymentDate
,GLA.GLAccount
FROM ac.LOCPayment LOC
INNER JOIN ac.GLAccount GLA on LOC.GLAccountId= GLA.GLAccountId
ORDER BY LEFT(GLA.GLAccount,6)

Open in new window

0
ApexCoAuthor Commented:
The order by will work with listing them out properly

But I need to do a new page for a break on each group, so I still need the group by functionality to work.
0
BodestoneCommented:
It is better in these cases to use the grouping in the report sopftware than in the query itself.
If yoo remove the grouping from the query then you can add it in the report and set new page on group as part of the report grouping options.
0
imitchieCommented:
In that case, I would add a new column into the query that you can set as the "Group by" column in your report or whatever is using this query.  You can omit the new column from being displayed in your reporting tool while still using it for page breaks.
SELECT LOC.GlAccountid
,LOC.PrinAmount
,LOC.IntAmount
,LOC.PaymentDate
,GLA.GLAccount
,GroupBy = LEFT(GLA.GLAccount,6)
FROM ac.LOCPayment LOC
INNER JOIN ac.GLAccount GLA on LOC.GLAccountId= GLA.GLAccountId
ORDER BY LEFT(GLA.GLAccount,6)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.