RE: Summary results in SQL

With QM forms it is possible to get some interesting results. A QMFORM is like 
the presentation side of Query/400 queries - QMQRYs are the data retrieval part 
of Query/400. You can specify summary functions in QMFORMs - it is a little 
tricky to get a summary-only equivalent. Query Manager is the preferred tool 
for generating QMFORMs, as the tag language used is not for the faint of heart. 
But see the Query Management Programming guide for more about this, as well as 
several nice examples.


-------------- Original message -------------- 
From: "Wilt, Charles" <CWilt@xxxxxxxxxxxx> 

Short answer...No. 

Long answer, SQL expects the query tool being used to provide the 
summary rows you are looking for. 

For a green screen equivalent, try Query Manager (STRQM). In query 
manger, you'd specify the query as: 
SELECT DISTINCT 
TPROD, 
TQTY, 
TQTY * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 

Then in the Query Manager Form, you'd specify the Column usage as SUM. 
When you run the query with the specified form, you'd get detail and 
summary lines on the report. 

Showcase should allow you the same capacity. 



In order to get a results set that actually contains detail and summary 
rows, you have to use a UNION. 
SELECT DISTINCT 
TPROD, 
sum(TQTY), 
sum(TQTY) * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 
Group by TPROD 
UNION 
SELECT DISTINCT 
TPROD, 
TQTY, 
TQTY * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 

HTH, 

Charles Wilt 
-- 
iSeries Systems Administrator / Developer 
Mitsubishi Electric Automotive America 
ph: 513-573-4343 
fax: 513-398-1121 

-----Original Message----- 
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
daparnin@xxxxxxxxxxxxxx 
Sent: Wednesday, November 01, 2006 11:31 AM 
To: midrange-l@xxxxxxxxxxxx 
Subject: Summary results in SQL 





This is the select statement for a Showcase query that's 
pulling data from 
the iSeries. On a typical green-screen query you can select 
summary or 
detail data from the "Select output type and output form" 
option. Can that 
be done with SQL? Basically, we just want to see a part 
number, the total 
quantity, and the total of the cost * quantity. If it helps, forget 
Showcase; how would you do this in green-screen SQL? 


SELECT DISTINCT 
TPROD, 
TQTY, 
TQTY * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 


Dave Parnin 
-- 
Nishikawa Standard Company 
Topeka, IN 46571 
daparnin@xxxxxxxxxxxxxx 

-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l. 

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l.