Re: SQL Break/total question

Good clarifications - I knew what I meant, just didn't say it the best.

I think this may say it better - the column list contains columns that are a subset of those in the GROUP BY, in addition to aggregate functions, which are optional, albeit desirable most of the time.

Does that make a reasonable, good-enough summary?

Cheers
Vern

On 2/23/2013 1:00 PM, CRPence wrote:

On 22 Feb 2013 08:32, Vernon Hamberg wrote:

Whatever you put in the GROUP BY has also to be in the column list.

No. However the effect is unlikely to be considered worthwhile for a
typical data set; the effect could be desirable in some cases. The
following is perfectly acceptable\valid to request [although probably
nonsensical for the given file.data]:

SELECT LGSESN, COUNT(*) AS SESNCNT
FROM anloga
GROUP BY LGSESN, LGDATE

<<SNIP>>
But the simple case would look like this -

SELECT LGSESN, LGDATE, COUNT(*) AS SESNCNT
FROM anloga
WHERE 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

Literally the /same/ expression in both the GROUP BY clause and the
SELECT list, not just figuratively, excepting white-space. For example
the expression (LGSESN CONCAT LEFT(LGSESN, 1)) is not the /same/
expression as (LGSESN || LEFT(LGSESN, 1)) even if we might presume they
should be; at least they were never considered the same in older
releases, and that specific example was verified on v5r3.

- and you can NOT have anything in the column list that is not in
the group <<SNIP>>

Only if having written /column list/ implies for when only columns
are specified in the SELECT list; i.e. ignoring that expressions may
also be specified, for which having stated "any column in the column
list" would have been clearer, even if sounding redundant :-)
_i Select list notation i_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzselectnotation.htm
"...
Parent topic: _i select-clause i_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzselectclause.htm
"

AFaIK only a /column name/ or a /non-aggregate column expression/
specified in the select-list [of columns and\or expressions] must be
specified also in the GROUP BY. But that requirement for
column-expressions [¿often?] can be satisfied by naming either the
column or the expression. Effectively if the expression involves a
column and can be evaluated using the value of the grouped column, then
the column(s) involved in the expression need only have the column named
in the GROUP BY clause; i.e. the expression itself need not be grouped.

The following SELECT operates fine [given LGSESN and LGDATE are
numeric] because the expressions involving the columns have those
columns being grouped, and thus the expressions can be evaluated using
the value of the named column for each group[ing]:

select int(mod(lgsesn,2)) as odd
, int(mod(lgsesn, lgdate)) as remxmp
/* omit lgsesn; e.g. just wanted to known even\odd */
, lgdate
, count(*)
from anloga
group by lgsesn, lgdate

Specifically of value may be knowing that the scalar function RAND()
can be selected but not grouped. As noted in the above, the function is
performed only for each group. And if a column is grouped, then that
column or a column-expression can define the seed as the argument for
the RAND(column-expression); again, the expression is evaluated using
the value of the column(s) in each group.

In contrast, the RRN() can not be evaluated for the value of a
grouped column, and thus is not an allowable function in the SELECT list
without being in the GROUP BY. But of course the RRN() being in the
GROUP BY would generate a group for every row selected and thus would
rarely if ever produce a worthwhile effect.

A scalar expression not involving a column [e.g. a constant or an
expression that is in effect a constant value such as a UDF or special
register, or even a random result <see RAND() earlier>] can occur in the
select\column list, yet not appear in the GROUP BY clause. The ability
to do so is imperative to combine multiple summary subselect results in
a UNION ALL query to mimic what eventually became available with the
ROLLUP\CUBE grouping-sets.

The following SELECT operates fine even though the expression used in
the SELECT list is omitted from the GROUP BY:

select lgsesn
, cast(null as date) lgdate
, count(*)
from anloga
group by lgsesn /* the second column\expression is not here */