Hello

I need to agregate in a SQL select text fields by a common ID in a calculated text field, something like SUM but with CHAR fields.
For example all the part numbers included in a shipment.
I tried with triggers and stored procedures but our DB is too and they aren't efficient.

Surelly is a silly question for you.

Thanks in advance.
Martin

Can you provide more information?

mdb wrote:

mdb, Without examples, I am not sure if this will help, but...
{code}SELECT PART_NO, COUNT(*) AS CNT
FROM table-name
GROUP BY PART_NO{code}
This will return a count for the distinct occurrences of PART_NO.

If you have the following PART_NO VALUES:
{code}PART_NO
ABC1
ABC1
BCD2
BCD2
BCD3
XYZ1
XYZ3{code}

The result would be:

{code}PART_NO CNT
ABC1 2
BCD2 2
BCD3 1
XYZ1 1
XYZ3 1{code}

Hello SDas

Thank you so much for your answer.
What I meant is to concatenate, or sum, the text fields.
For example:

SELECT SH_ID, SUM(PART_NO) AS PN, COUNT(*) AS CNT
FROM table-name
GROUP BY SH_ID

Having this PART_NO values...

SH_ID PART_NO
1 ABC1
1 ABC2
1 ABC3
2 BCD
2 EFG
2 HIJ
2 KLM

The result I would like to get is;

SH_ID PN CNT
1 ABC1ABC2ABC3 3
2 BCDEFGHIJKLM 4

I don't know if it is possible with standard SQL.
Thank you again for your support.

"mdb" <martin (AT) decoexsa (DOT) com> skrev i en meddelelse
news:2091131157.1251213467915.JavaMail.wassrvr (AT) ltsgwas009 (DOT) sby.ibm.com...
I'm only guessing, because you have provided no real information on what you
want to accomplish. Here is my best guess, based on your rather vague and
inconclusive information:

select t1.shipmentno, t1.partno, t1.price, t1.noofparts, t1.price *
t1.noofparts as partnovalue, tt.shipmentvalue
from shipments t1
inner join (
select shipmentno, sum(price * noofparts) as shipmentvalue
from shipments
group by shipmentno
) tt on t1.shipmentno=tt.shipmentno
order by t1.shipmentno, t1.partno

The result should look something like this:

ship1, part1, 6.18, 2, 12.36, 664.56
ship1, part7, 108.2, 1, 108.2, 664.56
ship1, part10, 322, 1, 322, 664.56
ship1, part12, 111, 2, 222, 664.56
ship2, part9, 250.58, 4, 1002.32, 1224.32
ship2, part12, 111, 2, 222, 1224.32

In other words - I'm guessing that what you want is something like a sum of
the value of the parts in a shipment duplicated for every row (partno.) of
the shipment.

BTW, the above query could also be written as:

With tt as (
select shipmentno, sum(price * noofparts) as shipmentvalue
from shipments
group by shipmentno)
select t1.shipmentno, t1.partno, t1.price, t1.noofparts, t1.price *
t1.noofparts as partnovalue, tt.shipmentvalue
from shipments t1
inner join tt on t1.shipmentno=tt.shipmentno
order by t1.shipmentno, t1.partno

First a temporary table (tt) is created that sums up the value of the whole
shipment.
This temp table is then joined to the original table.
(at least it's best explained that way. :-) I'm not sure if this is how the
final access plan is going to look once the database is ready to execute).