CODEWITHSUNDEEP
sqldb2db2-400
Geoff_S
Geoff_S

Reputation: 5107

Using different group by statements in DB2

I have a successful query which is returning some data for a user and category (the query is grouped by category and user) which is basically showing projections for each category per user

select 
    user, 
    category, 
    sum(CatProjection) as CatProjection, 
    sum(CatProjection)/52 as WeekCatProjection
from projections
where user = 123
group by user, category

And this returns what I want/expect:

USER  |   CATEGORY      |   CatProjection |  WeekCatProjection   
----------------------------------------------------------------
123   |   CategoryA     |   755000        |   14519              
123   |   CategoryB     |   390000        |   7500              
123   |   CategoryC     |   281250        |   5408

The issue is, those results are grouped by user and category, and now I want to add two columns which are dependent on the sum of CatProjection for that user, but not grouped by category.

So for the new value TotalCatProjection, I want to add all CatProjection values for that User. I then want to user CatPercentage to divide the WeekCatProjection by TotalCatProjection.

So now I would expect these results:

USER  |   CATEGORY      |   CatProjection |  WeekCatProjection   |  TotalCatProjection |  CatPercentage 
--------------------------------------------------------------------------------------------------------
123   |   CategoryA     |   755000        |   14519              |  27427              |    52.94
123   |   CategoryB     |   390000        |   7500               |  27427              |    27.35
123   |   CategoryC     |   281250        |   5408               |  27427              |    19.72

How can I get these 2 new columns which are based only on the USER when my main query is grouped by user AND category?

Upvotes: 0

Views: 53

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Since the window functions are introduced in DB2 7.3 you should use a subquery solution:

select user, 
    category, 
    CatProjection, 
    WeekCatProjection,
    TotalCatProjection,
    100 * WeekCatProjection / TotalCatProjection
from
(
    select 
        user, 
        category, 
        sum(CatProjection) as CatProjection, 
        sum(CatProjection)/52 as WeekCatProjection,
        ( 
           select sum(CatProjection) 
           from projections p2
           where p1.user = p2.user
        ) as TotalCatProjection
    from projections p1
    group by user, category 
) t

I avoid the where user = XYZ condition because then the GROUP BY user does not make a sense, however, you can insert it there if you want to.

Upvotes: 1

Related Questions