Hi Frank
Whatever you put in the GROUP BY has also to be in the column list.
Is there only one LGDATE per LGSESN? If so, put that in the column list
and GROUP BY. If not, you'll need to pick one - first or last or both
first and last to get a range.
But the simple case would look like this -
SELECT LGSESN, LGDATE, COUNT(*) AS SESNCNT FROM anloga HERE LGSESN NOT
LIKE '%ARCHIVE%' GROUP BY LGSESN, LGDATE order by LGSESN
You can use expressions, as I recall, in the GROUP BY, so long as there
is a matching one in the column list -and you canNOT have anything in
the column list that is not in the group - that does make some sense, eh?
HTH
Vern
On 2/22/2013 10:20 AM, fbocch2595@xxxxxxx wrote:
Hi Vern, your command gave me what I want...how can I add the LGDATE field to this and still get the break and total?
SELECT LGSESN, COUNT(*) AS SESNCNT FROM anloga
HERE LGSESN NOT LIKE '%ARCHIVE%'
ROUP BY LGSESN
rder by LGSESN
-----Original Message-----
From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Fri, Feb 22, 2013 11:13 am
Subject: Re: SQL Break/total question
Hi Frank
That would be GROUP BY to get aggregates. See if this gives you what
ou want -
SELECT LGSESN, COUNT(*) AS SESNCNT FROM anloga
HERE LGSESN NOT LIKE '%ARCHIVE%'
ROUP BY LGSESN
rder by LGSESN
By the way, this could perform rather poorly - I think it requires a
ull table scan, due to the NOT LIKE, for sure, and , even more,
ildcards on both ends of the comparison value. Unfortunately, I'm not
ure there is an alternative.
HTH
ern
On 2/22/2013 9:55 AM, fbocch2595@xxxxxxx wrote:
Hi Folks, this statement runs fine;
SELECT LGSESN, LGDATE FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
order by LGSESN
but if I want to only have each LGSESN output a single line with the total
umber of hits for all the LGSESN's, what's the syntax for that?
Is there any tip sheets or manuals for learning SQL?
Thanks, Frank
