On 25-Aug-2014 18:02 -0500, Lance Gillespie wrote:
Good point and that would work for that data set.
I was trying for a general syntax for the "add two columns
in a sum case where" so that it would work for this issue as well:
create table qtemp/ut420piv as
(select utcsid, utlcid, bilprd
, sum(case when UTRBCM = 'MU' then UTTAMT end) as MUREV
, sum(case when UTRBCM = 'SC' then UTTAMT end) as SCREV
, sum(case when UTRBCM = 'T1' then UTTAMT end) as T1REV
, sum(case when UTRBCM = 'T2' then UTTAMT end) as T2REV
, sum(case when UTRBCM = 'T3' then UTTAMT end) as T3REV
, sum(case when UTRBCM = 'T4' then UTTAMT end) as T4REV
, sum(case when UTRBCM = 'T5' then UTTAMT end) as T5REV
from qtemp/UT420SUBST
group by utcsid, utlcid, bilprd
order by utcsid, utlcid, bilprd
) with data
where I also want to sum from those same selections the
field UTCHCN into MUCONS, SCCONS, T1CONS, etc.
I am not sure I see the above as being anything unique from what was
already described, for selecting data as-rows vs as-columns; the only
conspicuous difference being, that there are only twelve known months,
whereas there are an indeterminate amount of the UTRBCM values.? Yet
even for month values, a potential extra challenge exists for any month
with no data in that month, when summarizing data as rows; that is
because no row is generated by\as default, whereas the column is
explicitly being generated when summarizing the data as columns.
In either case, with months or a list of values, the full list can be
generated from a parent file [or from the data in the file itself] or as
a row-values clause. In the following query, I simply move the literal
values [SQL calls them constants] from the CASE expressions into an IN
predicate of the WHERE clause; that could be expanded to include all
possible values using a subselect instead, but in a WHERE clause vs as
data used for a JOIN with defaults, probably not useful to do, because
that predicate [the entire WHERE clause in the following] could simply
be omitted to be inclusive of all values:
select utcsid, utlcid, bilprd, UTRBCM
, sum(UTTAMT) as TAMT_tot
, sum(UTCHCN) as CHCN_tot
from qtemp/UT420SUBST
where UTRBCM in ('MU','SC','T1','T2','T3','T4','T5')
group by utcsid, utlcid, bilprd, UTRBCM
order by utcsid, utlcid, bilprd
and the selection is not exhaustive. There are many records that
this selection omits.
If the list of values comes from a file [instead of coded as literals
within the SELECT statement], the results of the above query generating
data _as rows_ adjusts automatically, whereas the result _as columns_
requires updating the query to add a new CASE expression to define the
new column for the new literal\constant value being selected.
I suppose I can run this twice, once to sum UTRBCM and once for
UTCHCN and join the results.
The CASE expressions can be repeated for the other columns for which
summaries will being generated [as noted already in the reply by Sam];
that can be done in the one query, so no reason to perform any join.
But to obtain the data as rows vs as columns, a JOIN may be the most
preferable means _to effect including generated rows_ for any missing
[as matching] values; e.g. something like the [untested] following
query, wherein a row values-clause produces the list of items for
selection much like the IN predicate values?:
with
rbcm_list (xxRBCM) as
( select vc.*
from table
(values('MU'),('SC'),('T1'),('T2'),('T3'),('T4'),('T5')) as vc
)
select utcsid, utlcid, bilprd, xxRBCM
, TAMT_tot
, CHCN_tot
from rbcm_list as rl
left outer join lateral
( select ut.*
, sum(UTTAMT) as TAMT_tot
, sum(UTCHCN) as CHCN_tot
from qtemp/UT420SUBST as ut
where ut.UTRBCM = rl.xxRBCM
group by utcsid, utlcid, bilprd, UTRBCM
) as lt
on rl.xxRBCM = lt.UTRBCM
order by utcsid, utlcid, bilprd
Again, as with my previous reply [as I recall alluding anyhow], if
the list of values being selected is maintained in a file, then when
that file is used in a subselect to effect selection [vs the literals
listed in the row-values-clause] as a modification to the above query,
the selection can be changed dynamically by updating that table vs by
updating the query. Conspicuously, that is not an option in the
data-as-columns query when composing the query manually; that query must
be written to include a new column for the new datum. Obviously the
query could be generated dynamically from the data in a separately
maintained file of selection-criteria but the query is not static; the
above query can be declared as a static\embedded query with the CTE
having been changed to a query of the selection-criteria-table vs its
being a derived table of static data [as written above using the
values-clause].
As an Amazon Associate we earn from qualifying purchases.