Wsrtjtyk

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?

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.