RE: SQL : subtotals and total

I said it before number of times and I'll keep repeating it till I'm blue in
the face. For performance reasons, use UNION ALL instead of simply UNION,
whenever you can (i.e. result sets are identical with either version).
Version without the ALL qualifier performs DISTINCT processing
unconditionally.
It's just a good habit to get into, and you'll notice that in Charles'
example he's used the ALL qualifier.

HTH, Elvis


Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL : subtotals and total

Great thanks Alan,

Now I have

SELECT
SPTFCD, '10' AS CODE_STATISTIQUE, SPRDCD, SSLMNV, SUM(SOCCNB) AS
PROPOSITIONS
FROM SVQSTS WHERE SRBQCD = 'TOT2'
AND SOCCNB <> 0
GROUP BY SPTFCD, SPRDCD, SSLMNV

union
SELECT
SPTFCD, '10' AS CODE_STATISTIQUE, SPRDCD, ' ', SUM(SOCCNB) AS
PROPOSITIONS
FROM SVQSTS WHERE SRBQCD = 'TOT2'
AND SOCCNB <> 0
GROUP BY SPTFCD, SPRDCD


Which gives me ( I still have to verify ! ) subtotals by SSLMNV followed by
a grand total.