Query Advice

[Mighty]

I have data in the following format

PRTNUM COMCDE QTY
part1 ABA 100
part2 BAA 150
part3 CA 100
part4 NS135 1000
part5 ABB 200
part6 DB 500

Parts that have a COMCDE field that begins with either a, B or C need to be group together under an FG column and parts with COMCDE starting with anything other than A, B or C need to be grouped as RM

What I want to end up with is the following:

TYPE QTY
FG 550
RM 1500

Can anyone advise me on how best to do this?

Mighty

 

[Linda84]

Try something like:

Select IF(UCASE(LEFT(COMCDE, 1)) IN ('A', 'B', 'C'), 'FG', 'RM') as cc, sum(QTY)
from TableName
group by cc

Linda
Pervasive Software

 

[Mighty]

I had tried that because it works in access - but Pervasive didn't like it.

Mighty

 

[Linda84]

Strange... What version of Pervasive are you using? I tried it via PCC on some little sample tables I made in v9.1 and it worked fine. When you tried it, did you get an error or incorrect results?

Linda
Pervasive Software

 

[Mighty]

You're right. I must have done something stupid first time around. The actual data is in two separate tables and the following worked for me:

Select IF(UCASE(LEFT("Part Master".COMCDE_01, 1)) IN ('A', 'B', 'C'), 'FG', 'RM') as cc, sum("Part Stock".QTYOH_06)
from "Part Master" INNER JOIN "Part Stock" ON "Part Master".PRTNUM_01 = "Part Stock".PRTNUM_06
group by IF(UCASE(LEFT("Part Master".COMCDE_01, 1)) IN ('A', 'B', 'C'), 'FG', 'RM')

Mighty