• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

Aggregation Problem - SubQuery?

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
FindlayIT
Asked:
FindlayIT
  • 5
  • 4
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
FindlayITAuthor Commented:
Not sure on the version Kent. How do I check?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
tliottaCommented:
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
 
FindlayITAuthor Commented:
Won't do ROLLUP. What is the hard way?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
tliottaCommented:
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
 
FindlayITAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
FindlayITAuthor Commented:
It took me awhile to work through it but it works! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now