Solved

Grouping Codes In Sql

Posted on 2011-02-16
7
328 Views
Last Modified: 2012-05-11
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
Comment
Question by:pepps11976
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34906025
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
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 34906062
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
 

Author Comment

by:pepps11976
ID: 34906085
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 13

Accepted Solution

by:
darren-w- earned 500 total points
ID: 34906086
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
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34906141
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
 

Author Comment

by:pepps11976
ID: 34906610
Ok Guys i will follow the instructions of Darren-W and see how i get on.

Sorry about the Title.
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL syntax for max(date) 3 39
database audit for object access 6 42
SQL query 45 41
first parameter x of the decimal (x, y) 1 30
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question