|
Try some variation of this:--
SELECT
case when invoice is null then 'Grand' else char(invoice) end,
case when item is null then 'Total' else char(item) end,
sum(dollars)
FROM salestable
group by rollup(invoice, item)
order by invoice, item
Steve Needles
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Smith, Mike
Sent: Friday, July 18, 2014 3:22 PM
To: Midrange Systems Technical Discussion
Subject: RE: single sql to contain details and totals.
Jeff,
That's what I'm using, but I'm not getting my detail records.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Jeff Young
Sent: Friday, July 18, 2014 4:14 PM
To: Midrange Systems Technical Discussion
Subject: Re: single sql to contain details and totals.
Mike,
Look at the WITH ROLLUP clause on the GROUP BY.
I think that will do what you want.
Jeff Young
Sr. Programmer Analyst
On Fri, Jul 18, 2014 at 4:02 PM, Carel Teijgeler
<coteijgeler@xxxxxxxxx>
wrote:
Mike,--
You mix Query statements with Query forms (in which you can define a
lay out with sub and end totals).
To get this in your query
You have to do a UNION and sort on The last field (FLXD,FLXM) and
another sorting field related to the joined queries.
Regards,
Carel Teijgeler
On 18-7-2014 21:13, Smith, Mike wrote:
I'm trying to convert a QM400 query to a rpg program.This is the Midrange Systems Technical Discussion (MIDRANGE-L)
Its fairly simple and I'd be done already if I just coded my breaks
in the program, but I'm trying to expand my sql knowledge.
I found an example from an article and can actually get it to work,
but I cannot get my own to work.
My query joins 2 files and simply sums up a deduction amount when
there is a break(the break is at company,gl,ded code) I want to
print the details followed by the totals for the deduction amount.
3300 110,023,220,130,100 41.67 FLXD
3300 110,023,220,130,100 41.67 FLXD
TOTAL 83.34
3300 110,023,220,140,100 40.00 FLXM
3300 110,023,220,140,100 50.00 FLXM
TOTAL 90.00
The query I got to work is from the following
http://odetocode.com/articles/85.aspx
here is my query that I can't get to work
select
case
when(Grouping(pdhccd)=1) then 'company totals'
else pdhccd
end as pdhccd,
case
when (Grouping(pdhgln)=1) then 'Gl Totals'
else pdhgln
end as pdhgln,
case
when (Grouping(pdhded)=1) then 'ded Totals'
else pdhded
end as pdhded,
sum(pdheed) as emptot
from prdhs join PMST on Pdhemp = pmemp where pdhedt between
20140501 and 20140531 and pdhgln in(select glno from prflexgl00)
and pdheed <> 0 group by pdhccd,pdhgln,pdhded with rollup
I only get the summary records and not the details.
I probably only need the GL total or ded total case statement,
because they change at the same time, and I've tried that, but I
get basically the same result.
Any idea what I've got wrong.
If there is an easier way, please let me know
Mike
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received
this message in error, or are not the named recipient(s), please
immediately notify the sender and delete this message.
--
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.
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this
message in error, or are not the named recipient(s), please
immediately notify the sender and delete this message.
--
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 communication, including attachments, is confidential, may be
subject to legal privileges, and is intended for the sole use of the
addressee. Any use, duplication, disclosure or dissemination of this
communication, other than by the addressee, is prohibited. If you have
received this communication in error, please notify the sender
immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
