Solved

Aggregation Problem - SubQuery?

Posted on 2008-10-09
11
868 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
In a recent article here at Experts Exchange (http://www.experts-exchange.com/articles/18880/PaperPort-14-in-Windows-10-A-First-Look.html), I discussed my nine-month sandbox testing of the Windows 10 Technical Preview, specifically with respect to r…
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.

910 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

20 Experts available now in Live!

Get 1:1 Help Now