[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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

0
ApexCo
Asked:
ApexCo
  • 5
  • 3
  • 2
  • +1
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now