Solved

Aggregation Problem - SubQuery?

Posted on 2008-10-09
11
870 Views
Last Modified: 2013-12-06
I run into aggregation problems when I have a table with detail that I need to sum to join with a table with summary information. Example is a table with vehicle repair orders by repair line. I join it with a labor table that has totals by repair line. No problems so far. Then I join with the parts table that has multiple rows (i.e. individual parts used on each line - many times more than one part per repair line) per repair line and I want to sum the price and cost of the parts by line.

I get double and triple the correct amounts when I do a SUM on those two columns from the Parts detail table (depending on - I think - the number of rows per repair line.

Will a subquery work for those two columns? I have attached the entire statement and you can see the two sum columns (the only two)

This is for an Iseries
Thanks
RepairOrderSQL.txt
0
Comment
Question by:FindlayIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22681277
Hi Findlay,

The join and aggregation are correct.  That's the way they work.

It sounds like what you really want is a rollup.  Part1 $xxx.xx / Part2 $xxx.xx / Part3 $xxx.xx / Total $xxx.xx.


That functionality is fairly new on the AS/400.  What version are you running?

If the functionality isn't there, you'll have to build the report the hard way, with detail lines and summary lines that you generate.


Good Luck,
Kent
0
 

Author Comment

by:FindlayIT
ID: 22681308
Not sure on the version Kent. How do I check?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22681527
Easiest is to see if the functionality exists.

Do any simple query with an aggregation:

SELECT warehouse, part_number, sum(onhand)
FROM mydatebase
GROUP BY ROLLUP (warehouse, part_number);

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:tliotta
ID: 22681677
FindlayIT:

> Not sure on the version Kent. How do I check?

Easiest way is with the WRKLICINF (Work with License Information) command. Enter it on a command line and press [enter]. The resulting display should list a bunch of licensing elements.

Usually the first item listed will be a product numbered like "5722SS1" with a description like "Operating System/400" or "i5/OS". The 'License Term' column will be sufficient for your DB2 version. It will look like "V5R4M0" with six characters. Others in the list may only have "V5" or even very different digits.

The license term of the base operating system itself is what you want.

Tom
0
 

Author Comment

by:FindlayIT
ID: 22682268
Won't do ROLLUP. What is the hard way?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22683606
You'll basically need to do 2 reports.  1 for the detail, and 1 for the summary.  You can UNION ALL them into a single report, but it'll take two (sub-)queries.


Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22684134
FindlayIT:

Without knowing what version/release your system is at, it's pretty difficult to try to give good answers. For recent releases, "common table expressions" (CTEs) have been available that might let you create a readable statement that gives desired results.

Review CTEs for V5R4 here:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafzmstintsel.htm

While reviewing, pay close attention first to topic 'Example 1: Single level explosion', and then to 'Example 2: Summarized explosion'.

Tom
0
 

Author Comment

by:FindlayIT
ID: 22704723
Kent, The detail (parts) needs to be summed and put on the summary row. The way I understand UNION that just will put detail rows in with summary rows??
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22704834
Well, there are always ways.  ;)

But some are clearly more ugly than others.  If your final report is to be several items with sub-totals at periodic intervals, you can run the two reports with an extra column, merge (UNION ALL) them, and sort them.  Ugly, but it works.

Totalling by F1, F2, concept is:

SELECT 1, F1, F2 FROM xxx
UNION ALL
SELECT 2, F1, F2, SUM(F3) as F3, COUNT(F4) as F4 FROM xxx
GROUP BY F1, F2
ORDER BY 2,1;

That won't work as the number of returned columns must be consistent, so:

SELECT 1, F1, F2, 0 as F3, 0 as F4 FROM xxx
UNION ALL
SELECT 2, F1, F2, SUM(F3) as F3, COUNT(F4) as F4 FROM xxx
GROUP BY F1, F2
ORDER BY 2,1;

That leaves ugly detail, as there is a '0' where blank is preferred, so:

SELECT 1, F1, F2, ' ' as F3, ' ' as F4 FROM xxx
UNION ALL
SELECT 2, F1, F2, cast (SUM(F3) as char(10))as F3, case (COUNT(F4) as char(10)) as F4FROM xxx
GROUP BY F1, F2
ORDER BY 2,1;

That may left justify the SUM and COUNT values, so..

etc...

This gets silly, but it will generate a respectable looking report.
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 22704851
And of course, the final version will likely be something akin to:

Select F1, F2, F3, F4
FROM
(
  SELECT 1, F1, F2, ' ' as F3, ' ' as F4 FROM xxx
  UNION ALL
  SELECT 2, F1, F2, cast (SUM(F3) as char(10))as F3, case (COUNT(F4) as char(10)) as F4FROM xxx
  GROUP BY F1, F2
  ORDER BY 2,1

) as t0;

Kent
0
 

Author Closing Comment

by:FindlayIT
ID: 31504770
It took me awhile to work through it but it works! Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question