niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » IBM DB2
Help for a SQL Query statement: group by
Post new topic   Reply to topic Page 1 of 1 [4 Posts] View previous topic :: View next topic
Author Message
roberto
*nix forums addict


Joined: 28 Feb 2005
Posts: 96

PostPosted: Mon Jul 17, 2006 12:43 pm    Post subject: Help for a SQL Query statement: group by Reply with quote
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
Back to top
Serge Rielau
*nix forums Guru


Joined: 29 Apr 2005
Posts: 1583

PostPosted: Mon Jul 17, 2006 1:06 pm    Post subject: Re: Help for a SQL Query statement: group by Reply with quote
roberto wrote:
Quote:
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/ondemandbusiness/conf2006/
Back to top
roberto
*nix forums addict


Joined: 28 Feb 2005
Posts: 96

PostPosted: Mon Jul 17, 2006 1:35 pm    Post subject: Re: Help for a SQL Query statement: group by Reply with quote
Thank You very much!!!

A very quicky reply! I posted only few minutes ago! Smile)
Bye
Back to top
Brian Tkatch
*nix forums Guru Wannabe


Joined: 28 Jul 2005
Posts: 295

PostPosted: Mon Jul 17, 2006 2:05 pm    Post subject: Re: Help for a SQL Query statement: group by Reply with quote
roberto wrote:
Quote:
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.
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [4 Posts] View previous topic :: View next topic
The time now is Mon Dec 01, 2008 11:42 pm | All times are GMT
navigation Forum index » Databases » IBM DB2
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Select statement Shamna Sybase 0 Mon Sep 17, 2007 6:03 am
No new posts Very slow query Michael Sutter MySQL 0 Fri Jul 21, 2006 1:10 pm
No new posts ECPG (usage of simple select statement) Jasbinder Bali PostgreSQL 0 Fri Jul 21, 2006 3:28 am
No new posts Column info without executing query Dan Strömberg PostgreSQL 10 Thu Jul 20, 2006 6:21 pm
No new posts recursive query Jürg Schaufelberger Server 1 Thu Jul 20, 2006 6:06 pm

Credit Cards | Debt Help | Mortgages | Mobile Phone | Pink Ranger
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.2427s ][ Queries: 16 (0.1363s) ][ GZIP on - Debug on ]