CODEWITHSUNDEEP
sqldb2-400
Abercrombieande
Abercrombieande

Reputation: 709

SQL Group by many individual columns

I am not sure what to call this or what to search for. Basically I want to sum an amount by many individual columns. The only way I can think of to do this is to do a subquery for each column.

Is there an easier way to do this? Thanks

select q1.id, q1.amt state, q2.amt city ...
from 
 (select  id,state,sum(paid) amt
 from tableA
 group by id,state) q1
inner join 
 (select  id,city,sum(paid) amt
  from tableA
  group by id,city) q2
on q1.id = q2.id

Upvotes: 0

Views: 100

Answers (2)

Abercrombieande
Abercrombieande

Reputation: 709

Thanks to Gordon for the insight to use OLAP functions. The same OLAP functions like "partition by" are not available for db2 for i. I ended up with this, which I will have to wrap to sum up each column

select id,
case when GROUPING(STATE) = 0
     then sum(PAID)
end STATE,
case when GROUPING(CITY) = 0
     then sum(PAID)
end CITY ...
FROM TABLEA
GROUP BY ID, GROUPING SETS((ID,STATE),(ID,CITY)....)

GROUPING SETS creates records not columns for each group GROUPING indicates what set the record belongs to (when 0)

Example:

GROUPING

ID  STATE CITY PAID
1A   0     1   500.00
1A   1     0   250.00

USING THE GROUPING TO SEE WHERE TO ALLOCATE PAID

ID  STATE  CITY 
1A  500.00 NULL
1A  NULL   250.00

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I am going to guess that, for each id, you want the sum of amt along each dimension, such as city and state.

If so, you can use window/analytic/olap functions. Something like:

select id, sum(amt) over (partition by state) as state_amt,
       sum(amt) over (partition by city) as city_amt
from tableA;

Upvotes: 1

Related Questions