Help for a SQL Query statement: group by

[roberto]

I have a table (ORDERS) with this kind structure (this is not the
really table):

OrderId Product Amount Agent1 Agent2 Agent3
________ _______ ______ ______ ______ ______
000001 P1 20 AA BB XX
000002 P2 7 BB CC
000003 P2 12 CC
If i want summarize Amount by Product it's enought to write

Select Product, sum(Amount) from ORDERS group by Product

and so my results will be:

Product Amount
_______ ______
P1 20
P2 19
Now i want to summarizy by Agent Code, but Agent is "splitted" in 3
columns (Agent1, Agent2, Agent3)

It's possible using only a select statement obtain it?

I want see:

Agent Amount
_____ ______
AA 20
BB 27
CC 19
XX 20
I think it's not possible?

I'm using db2 8.2 and db2/400 (v5r3)

Best Regards
Roberto

[Serge Rielau]

roberto wrote:

I have a table (ORDERS) with this kind structure (this is not the
really table):

OrderId Product Amount Agent1 Agent2 Agent3
________ _______ ______ ______ ______ ______
000001 P1 20 AA BB XX
000002 P2 7 BB CC
000003 P2 12 CC
If i want summarize Amount by Product it's enought to write

Select Product, sum(Amount) from ORDERS group by Product

and so my results will be:

Product Amount
_______ ______
P1 20
P2 19
Now i want to summarizy by Agent Code, but Agent is "splitted" in 3
columns (Agent1, Agent2, Agent3)

It's possible using only a select statement obtain it?

I want see:

Agent Amount
_____ ______
AA 20
BB 27
CC 19
XX 20
I think it's not possible?

This is a pretty evil schema and the preferred solution would be to make
away with the Agent1..Agent3 columns (have one row per agent)
Anyway here is a way to do it. It works for sure in DB2 V8.2 for LUW
I don't have an iSeries available to quickly test there.

SELECT Product, Agent, SUM(Amount)
FROM
(SELECT O.Product, O.Amount, A.Agent
FROM ORDERS O, TABLE(VALUES(O.Agent1),
(O.Agent2),
(O.Agent3)) AS A) AS pivot
GROUP BY Product, Agent

If that does not work on iSeries you can replace the pivot subquery with
a 3-way UNION ALL... slower.

Cheers
Serge

PS: TABLE should really be LATERAL (ANSI SQL). It's available in DB2 for
iSeries and DB2 for LUW, but I don't recall the exact release for iSeries.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[roberto]

Thank You very much!!!

A very quicky reply! I posted only few minutes ago! :))
Bye

[Brian Tkatch]

roberto wrote:

I have a table (ORDERS) with this kind structure (this is not the
really table):

OrderId Product Amount Agent1 Agent2 Agent3
________ _______ ______ ______ ______ ______
000001 P1 20 AA BB XX
000002 P2 7 BB CC
000003 P2 12 CC
If i want summarize Amount by Product it's enought to write

Select Product, sum(Amount) from ORDERS group by Product

and so my results will be:

Product Amount
_______ ______
P1 20
P2 19
Now i want to summarizy by Agent Code, but Agent is "splitted" in 3
columns (Agent1, Agent2, Agent3)

It's possible using only a select statement obtain it?

I want see:

Agent Amount
_____ ______
AA 20
BB 27
CC 19
XX 20
I think it's not possible?

I'm using db2 8.2 and db2/400 (v5r3)

Best Regards
Roberto

Depending on how large the TABLE is, perhaps the simple solution would
be to create a VIEW on the TABLE separating the three agent COLUMNs.

CREATE VIEW Orders_Agent AS
SELECT OrderId, Product, Amount, Agent1 Agent WHERE Agent1 IS NOT NULL
UNION ALL
SELECT OrderId, Product, Amount, Agent2 Agent WHERE Agent2 IS NOT NULL
UNION ALL
SELECT OrderId, Product, Amount, Agent3 Agent WHERE Agent3 IS NOT NULL

And run the GROUP BY on the VIEW rather than the TABLE.

Of course, instead of a VIEW it could just be in the FROM caluse
directly.

B.