Re: Another SQL question

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].