Solved

Aggregation Problem - SubQuery?

Posted on 2008-10-09
11
867 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:
Kdo 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now