Did you try "WITH ROLLUP"
SELECT odldat, count(odobnm) as NbrObjects
FROM fbdod
where odldat = 090713 or odldat = 090813
group by odldat WITH ROLLUP
order by odldat
-Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of fbocch2595@xxxxxxx
Sent: Monday, September 09, 2013 10:49 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL question
This cmd;
SELECT odldat, count(odobnm) as NbrObjects
FROM fbdod
where odldat = 090713 or odldat = 090813
group by odldat
order by odldat
displays this;
Change NBROBJECTS
Date
090713 188
090813 1,483
******** End of data ********
So, at this point I want a total of the NBROBJECTS
-----Original Message-----
From: rob <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Mon, Sep 9, 2013 11:42 am
Subject: Re: SQL question
Give an example result table.
ob Berendt
-
BM Certified System Administrator - IBM i 6.1
roup Dekko
ept 1600
ail to: 2505 Dekko Drive
Garrett, IN 46738
hip to: Dock 108
6928N 400E
Kendallville, IN 46755
ttp://www.dekko.com
From: fbocch2595@xxxxxxx
o: midrange-l@xxxxxxxxxxxx,
ate: 09/09/2013 11:27 AM
ubject: Re: SQL question
ent by: midrange-l-bounces@xxxxxxxxxxxx
ot exactly, I want the output to show the 3 fields but total the # of
dobnm's. Thanks to all, for giving it a shot.
hanks, Frank
----Original Message-----
rom: rob <rob@xxxxxxxxx>
o: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
ent: Mon, Sep 9, 2013 11:04 am
ubject: Re: SQL question
ou mean like this?
ith T1 as (
LECT odldat, count(odobnm) as NbrObjects
OM myfile
ere odldat = 090713 or odldat = 090813
oup by odldat
der by odldat )
LECT myfile.odobnm, myfile.odldat, myfile.odltim, T1.NbrObjects
OM myfile Join T1 on myfile.odldat=T1.odldat
der by odldat
ould look rather goofy though:
DOBNM ODLDAT ODLTIM NBROBJECTS
GOODPRGM 090713 125537 2
OTHRPRGM 090713 062233 2
ISOBJECT 090813 041522 3
ATOBJECT 090813 193217 3
HROBJECT 090813 220515 3
ob Berendt
M Certified System Administrator - IBM i 6.1
oup Dekko
pt 1600
il to: 2505 Dekko Drive
Garrett, IN 46738
ip to: Dock 108
6928N 400E
Kendallville, IN 46755
tp://www.dekko.com
rom: fbocch2595@xxxxxxx
: midrange-l@xxxxxxxxxxxx,
te: 09/09/2013 10:38 AM
bject: Re: SQL question
nt by: midrange-l-bounces@xxxxxxxxxxxx
Bob, yea that works and that's something I could run...but I wish that
r SQL would report on all fields but total/count odobnm. Also, thanks
b for this;
ELECT odldat, count(odobnm) as NbrObjects
OM myfile
ere odldat = 090713 or odldat = 090813
oup by odldat
der by odldat
-----Original Message-----
om: Bob Levad <blevad@xxxxxxxxxxxxxxxx>
: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx>
nt: Mon, Sep 9, 2013 10:17 am
bject: RE: SQL question
ry this:
LECT COUNT(odobnm)
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
---Original Message-----
m: midrange-l-bounces@xxxxxxxxxxxx [
ilto:midrange-l-bounces@xxxxxxxxxxxx]
Behalf Of fbocch2595@xxxxxxx
t: Monday, September 09, 2013 9:01 AM
midrange-l@xxxxxxxxxxxx
ject: Re: SQL question
Mark, that doesn't work, says not valid;
lumn ODOBNM or expression in SELECT list not valid.
LECT COUNT(odobnm), odobnm, odldat, odltim
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
ORDER BY odldat
ELECT COUNTodobnm), odobnm, odldat, odltim
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
ORDER BY odldat
----Original Message-----
m: Mark S Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
t: Mon, Sep 9, 2013 9:44 am
ject: Re: SQL question
LECT COUNTodobnm), odobnm, odldat, odltim
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
ORDER BY odldat
On 9/9/2013 9:23 AM, fbocch2595@xxxxxxx wrote:
Folks, when I use the following statement I get a list of files but now
t to know the count or total of the odobnm's in the query...what's the
ntax
that or how can I get that number within by adding to the select
atement?
anks, Frank
ELECT odobnm, odldat, odltim FROM myfile
ere odldat = 090713 or odldat = 090813
der by odldat
is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
ost a message email: MIDRANGE-L@xxxxxxxxxxxx
ubscribe, unsubscribe, or change list options,
t:
http://lists.midrange.com/mailman/listinfo/midrange-l
mail: MIDRANGE-L-request@xxxxxxxxxxxx
re posting, please take a moment to review the archives
ttp://archive.midrange.com/midrange-l.
s is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
post a message email: MIDRANGE-L@xxxxxxxxxxxx
subscribe, unsubscribe, or change list options,
it:
http://lists.midrange.com/mailman/listinfo/midrange-l
email: MIDRANGE-L-request@xxxxxxxxxxxx
ore posting, please take a moment to review the archives
http://archive.midrange.com/midrange-l.
is electronic transmission and any documents accompanying this
ectronic
nsmission contain confidential information belonging to the sender. This
formation may be legally privileged. The information is intended only for
he
of the individual or entity named above. If you are not the intended
ipient, you are hereby notified that any disclosure, copying,
stribution,
the taking of any action in reliance on or regarding the contents of
is
ctronically transmitted information is strictly prohibited.
s is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
post a message email: MIDRANGE-L@xxxxxxxxxxxx
subscribe, unsubscribe, or change list options,
it:
http://lists.midrange.com/mailman/listinfo/midrange-l
email: MIDRANGE-L-request@xxxxxxxxxxxx
ore posting, please take a moment to review the archives
http://archive.midrange.com/midrange-l.
-
is is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
st
post a message email: MIDRANGE-L@xxxxxxxxxxxx
subscribe, unsubscribe, or change list options,
sit:
http://lists.midrange.com/mailman/listinfo/midrange-l
email: MIDRANGE-L-request@xxxxxxxxxxxx
fore posting, please take a moment to review the archives
http://archive.midrange.com/midrange-l.
-
is is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
post a message email: MIDRANGE-L@xxxxxxxxxxxx
subscribe, unsubscribe, or change list options,
sit:
http://lists.midrange.com/mailman/listinfo/midrange-l
email: MIDRANGE-L-request@xxxxxxxxxxxx
fore posting, please take a moment to review the archives
http://archive.midrange.com/midrange-l.
midrange.com
