Well, that gets you into a bit of a mess, but still easy to do once you get the knack....
In this case, you're actually combining TWO summary queries, and then combining them to produce one report. Where is gets tricky is forcing the final result to place the grand totals at the bottom (or top, depending on requirements).
Here's the original sample expanded to incorporate the new requirements (using CTE to help clarify the process):
//----------------------------------------------------------------------------------
with
RptBdy (lvl, CustNo, CustName, TtlSls, TtlCost, TtlOrders) as
(
Select '1', c.CustNo, c.CustName,
sum(s.InvTtl$),
sum(s.InvCost),
count(distinct s.Order#)
From CustMst c left outer join
InvHist s on (c.CustNo=s.CustNo and s.OrDate >= (Current_Date - 1 years))
Where c.CustStatus = 'Active'
Group By c.CustNo, c.CustName
),
//--------------
GrndTtl (lvl, CustNo, CustName, TtlSls, TtlCost, TtlOrders) as
(
Select '2', 0, '*GRAND TOTAL........',
sum(TtlSls),
sum(TtlCost),
sum(TtlOrders)
From RptBdy
),
//--------------
Report (lvl, CustNo, CustName, TtlSls, TtlCost, TtlOrders) as
(
Select * from RptBody
UNION
Select * from GrndTtl
Order by lvl, TtlSls desc
)
//--------------
Select CustNo, CustName, TtlSls, TtlCost, TtlOrders
From Report
//----------------------------------------------------------------------------------
As you can see, there's a lot going on here. We first produce the RptBdy result set, as we did in the original sample. Then we query RptBdy to produce the GrndTtl, followed by the UNION to combine the two. Note that I add a field called lvl to act as a sequence field. The UNION appends the two result sets and orders the output by lvl then TtlSls (desc).
We then want to remove the lvl field for output. This is done in the final SELECT, where we simply omit the lvl column.
hth,
Eric
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Don Cavaiani
Sent: Tuesday, October 02, 2007 11:17 AM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY
Eric - I tried this and it works well! How would the GRAND Totals be
included?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Tuesday, October 02, 2007 9:46 AM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY
Not really... It's quite easy to produce summary data in SQL....
Note all the fields that you need calculate a summary result. All of
these fields will use functions like SUM(), MIN(), MAX(), AVG() and so
forth.
All other fields (Keys, control breaks, etc.) need to specified in the
Group By clause. All of these fields in the Group By, taken together,
represent a DISTINCT grouping, and will result in a single row of the
result set. It works the same way as using a DISTINCT clause in a
simple select, where the DB finds every permutation of values that exist
in the DB for those columns, and returns a single row for each unique
grouping.
Here's a basic query using the Group By... The assignment might be
worded like "Produce a listing of all active customers, showing total
sales, cost, and a count of orders for the prior 12 months. Sort the
list by total sales (descending)."
Select c.CustNo, c.CustName,
sum(s.InvTtl$) as TtlSls,
sum(s.InvCost) as TtlCost,
count(distinct s.Order#) as TtlOrders
From CustMst c left outer join
InvHist s on (c.CustNo=s.CustNo and s.OrDate >= (Current_Date - 1
years)) Where c.CustStatus = 'Active'
Group By c.CustNo, c.CustName
Order By 3 desc
This query returns a row for all "Active" customers in the CustMst file
(see the Where clause), and joins to the Invoice history file to return
Sales, Cost, and OrderCount, if any. The two tables are joined on the
CustNo fields in both files, and is specified in the ON clause following
the table being joined. Note the clause with OrDate; this limits the
joined record to orders dated within the last year.
The placement of the date test is important. I put it in the ON clause,
because putting it in the Where clause would cause our query to
eliminate any rows where the join was null. Where clause applies to all
rows in a result set. If we were to test s.OrDate >= (Current_Date - 1
years) in the Where clause, we would remove all customers that had no
sales in the past year. By moving this to the ON clause, we can
evaluate the date of invoice at the time that we select the records to
join. If a record fails to match the criteria, it is omitted from the
joined result.
The Group By lists the control break fields, and finally, the returned
result set is ordered descending on the third column in the result
(Total Sales).
hth,
Eric
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Fleming, Greg (ED)
Sent: Tuesday, October 02, 2007 8:22 AM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY
We're using STRQMQRY because we need to pass a parameter, and can't do
it with RUNQRY.
However, I reckon it may be easiest to just modify my query to produce a
file with the detail, then create a second query to summarize it to
printed output. Since the second query won't require a parameter, we
can just use RUNQRY on it.
Thanks
|-----Original Message-----
|From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
|bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
|Sent: Tuesday, October 02, 2007 9:04 AM
|To: Midrange Systems Technical Discussion
|Subject: Re: Summary Output with STRQMQRY
|
|If you're using a Query/400 definition, and you're not using RTVQMQRY
or
|some such thing, then why are you using STRQMQRY and not RUNQRY?
|If you ARE using RTVQMQRY, then you should modify the statement
produced
|and use GROUP BY.
|
|Rob Berendt
|--
|Group Dekko Services, LLC
|Dept 01.073
|PO Box 2000
|Dock 108
|6928N 400E
|Kendallville, IN 46755
|
http://www.dekko.com
|
|
|
|
|
|"Fleming, Greg \(ED\)" <GFLEMING@xxxxxxxxxxxxxxxxxxxx> Sent by:
|midrange-l-bounces@xxxxxxxxxxxx
|10/02/2007 08:54 AM
|Please respond to
|Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
|
|
|To
|"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc
|
|Fax to
|
|Subject
|Summary Output with STRQMQRY
|
|
|
|
|
|
|We're using STRQMQRY on a Query/400 QRYDFN. The query when run from
|Query/400 is defined to return summary records only, but when run from
|STRQMQRY in a CL program (or interactively), it returns detail.
|
|I found a post from 2003 which indicated a similar problem, and
|suggested running RTVQMFORM to generate a source file from the QRYDFN,
|then using CRTQMFORM to create a form to use on the STRQMQRY QMFORM
|parameter. I tried that, but I still get detail.
|
|Is there anyway to make this thing give me summary records only ?
|
|Thanks
|
|Greg Fleming
|
|Senior Programmer/Analyst
|
|Everglades Direct, Inc.
|
|
|
|--
|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.
--
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.
midrange.com
