Re: SQL Break/total question -- MIDRANGE-L

As always, thanks for the info.
Frank

-----Original Message-----
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l <midrange-l@xxxxxxxxxxxx>
Sent: Fri, Feb 22, 2013 1:35 pm
Subject: Re: SQL Break/total question

On 22 Feb 2013 08:46, Buck Calabro wrote:
<<SNIP>> But SQL allows us to combine separate SELECTS into one
result set via UNION. The caveat is that each SELECT in the UNION
must have the same columns. Well, the same number and type, but you
get the idea.
Compatible types, and values of those types, eligible to be CAST into
he ¿promoted? type; see type precedence and\or type promotion in docs.

Because the top query is selecting LGSESN and LGDATE, the bottom

query needs the same two columns, but COUNT(*) isn't really either
one. So to make the two SELECTs compatible, I'd add a count to the
top and two dummy columns to the bottom:

SELECT LGSESN , LGDATE , count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN, LGDATE
UNION
SELECT '99999' , 0 , count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
order by 1

The trick with the UNION is the ORDER BY. Since there are multiple
SELECTs being lumped together, the column names aren't valid for the
sort. Use the column numbers instead.
FWiW: The UNION ALL is probably desired versus UNION. The latter is
synonym for UNION DISTINCT. Use of constants [aka literals] is
iscouraged, because the data must be well-understood and presumed; e.g.
s '99999' really always going to sort highest.? The /better/ choice is
he NULL value. The NULL value collates highest, and only the data type
ust be presumed. I also might assign summary "levels" to the query to
stablish order, especially if the column(s) might have the NULL value.
So for example I might code instead, one of the following [if OLAP
uery syntax is not available]:
[code]
SELECT dec(1, 1) as lvl /* level-1 summary; group 1 */
, LGSESN
, LGDATE
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN, LGDATE
UNION ALL /* do not do DISTINCT processing */
SELECT dec(0, 1) as lvl /* overall summary; group 0 */
, cast(NULL as char) /* typed /like/ LGSESN */
, cast(NULL as date) /* typed /like/ LGDATE */
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
/* implicit GROUP is all rows */
order by 2, 3 /* order by grouped values, and... */
, 1 desc /* overall summary follows summary group 1 */

SELECT dec(2, 1) as lvl /* level-2 summary; group 1 */
, LGSESN
, LGDATE
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN, LGDATE
UNION ALL /* do not do DISTINCT processing */
SELECT dec(1, 1) as lvl /* level-1 summary; group 1 */
, LGSESN
, cast(NULL as date) /* typed /like/ LGDATE */
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN
UNION ALL /* do not do DISTINCT processing */
SELECT dec(0, 1) as lvl /* overall summary; group 0 */
, cast(NULL as char) /* typed /like/ LGSESN */
, cast(NULL as date) /* typed /like/ LGDATE */
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
/* implicit GROUP is all rows */
order by 2, 3 /* order by grouped values, and... */
, 1 desc /* overall summary follows summary groups 2 and 1 */
[/code]