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.
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.

--

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.