Improve company productivity with a Business Account.Sign Up

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

Grouping Codes In Sql

Hi All

I have a table in Sql which consists of 293 Analysis Codes ie:

aa12345
aa123456
bb123
cc3456

What i need to be able to do is to group them in to certain products so for example

aa12345 and aa123456 need to be grouped and bb123 and cc3456 need to be grouped

so then i could create a bar graph for products showing which type of product has been sold.

so diodes for instance would consist of the aa12345 and the aa123456 anaylysis codes.

I am Using SQL Server 2008 Standard Edition

Any help on how to do this would be great.

Thanks

John
0
pepps11976
Asked:
pepps11976
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
hi

if i got you currect you want to order the values in groups not do a simple aggragate but any way.

1.
select analisyscodes from (
                                              select analisyscodes,
                                              case substring(analisyscodes,1,2) when 'aa' then 1
                                                                                                       when 'bb' then 2
                                                                                                       ....
                                                                                                       ....
                                                                                                       when 'zz' then 24 end ord ) as Tab
order by ord ASC

2.
select count(*) , substring(analisyscodes,1,2)
from table
group by substring(analisyscodes,1,2)
order by count(*) desc

                                                   
0
 
Paul_Harris_FusionCommented:
Groping codes sounds much more interesting then Grouping codes.....:)

However....
Do you have a table that contains the relationship between the codes and the groups you want to create?

You stated that :
aa12345 and aa123456 need to be grouped and bb123 and cc3456 need to be grouped
If this is correct and not a typo, then ashilo's response does not quite work for you since it will not group bb... and cc.... codes together.

What is it that determines that bb123 and cc3456 should be grouped together?
0
 
pepps11976Author Commented:
So at the moment i have this

SELECT DISTINCT it_anal
FROM         dbo.itran

which gives me this

AAMCSAMP
AERFERRI
AEROWEIN
CONYYREW
HERTUY
OUTGYU

for the above i would need AAMCSAMP,AEROWEIN grouped, And HERTUY and OUTGYU grouped

itried your example but it failed i probably have the syntax incorrect
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
darren-w-Commented:
Perhaps you should create another table called something like product type:

id   name
1   resistors
2   diodes

and then create a new column in you product table called product_type, putting your products in to their type:
id name                product_type
1   aa12345          1
2  aa123456         1
3  bb123                2
4  cc3456              2

then the query would be

select * from  analisyscodes ac
left join product_type pt
on ac. product_type = pt.id
where pt.id = 1
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

i would have to agree with @darren-w-
if there is no simple vivible corraletion between the values then you will have to create a refference table.
0
 
pepps11976Author Commented:
Ok Guys i will follow the instructions of Darren-W and see how i get on.

Sorry about the Title.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now