473,847 Members | 1,698 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help for a SQL Query statement: group by


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

Jul 17 '06 #1
3 2122
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/
Jul 17 '06 #2
Thank You very much!!!

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

Jul 17 '06 #3
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.

Jul 17 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
10241
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline View Query: Select Sq from ( Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
9
3143
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use SUBSTRING(ProductName, 1, CHARINDEX('(', ProductName)-2). I can get this result, but I had to use several views (totally inefficient). I think this can be done in one efficient/fast query, but I can't think of one. In the case that one query is not...
28
3312
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical make-believe challenge in order to avoid confusing the issue further. Suppose I was hosting a dinner and I wanted to invite exactly 12 guests from my neighborhood. I'm really picky about that... I have 12 chairs besides my own, and I want them all...
2
17767
by: Amanda | last post by:
From a guy in Microsoft newsgroups: | In *comp.databases.ibm-db2* there are always IBM guys | from the Toronto labs on line.Post with the | -for the love of god please help- | line and I'm sure you'll get their attention. | Their usually very good:) So here's my transplanted post ==========================================
2
2536
by: Jeff Blee | last post by:
I am hoping someone can help me. I am making a Access 97 app for a person and have run up against a problem to do with MS Graph. There is a table that has a number of data elements and a date field and entries are entered each month. A graph is required that has three of the data elements represented basically on the y axis and time as months along the x axis. So for each month there is a group of three columns representing the data...
0
2265
by: ward | last post by:
Greetings. Ok, I admit it, I bit off a bit more than I can chew. I need to complete this "Generate Report" page for my employer and I'm a little over my head. I could use some additional assistance. I say additional because I've already had help which is greatly appreciated. I do try to take the time and understand the provided script in hopes on not having to trouble others on those. But here it goes...
1
2326
by: David | last post by:
Hi, I cannot get the following (MS Access) SQL statement working in my asp page, please can anyone help me ? Thanks :-) ------------------------------------------------ <% strQuery = "SELECT Customers.CustomerName, OrderLines.JobNumber, Orders.PONumber, OrderLines.OrderQuantity,
6
2138
by: Twobridge | last post by:
I hope someone can help me out with my problem. I have found a sql statement that basically pulls all bills filed within a certain time period and the payments made on those bills with in the same time period. I group the payments by payment year and filed year which gives me a matrix with the filed year as the row and the pay year as the column....and this appears fine. My problem is that my employer does not want to see the...
0
2461
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in the whole. I really need MySQL wizz to give me a hand (and maybe refer me to books to get me to the wizz level myself). First off, english is a second language to me and sometimes my sentences might be a little awkward. Please forgive me. Mon...
6
1900
by: troy_lee | last post by:
I am trying to count the total number of units for a given part number that have a Priority rating of 1. Based upon some research, this is what I came up with for my query. Access says that I have invalid bracketing in the Select Distinct statement. The only brackets I inserted were to delimit the table's field names. Access did the rest. Also, Access created the second AS bracket group - . Am I barking up the right tree or is my query...
0
10983
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10647
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9482
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7882
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5911
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4528
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4119
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3164
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.