Post reply

Multi aggregation column CrossTab

  • April 19, 2006 at 9:04 am

    #111754

    Hi,

    I have a view with the following columns

    Task,

    Quarter,

    Hours,

    Cost,

    EstHours,

    EstCost

    I need the Sums of hours, cost, esthours, and estcost. The only way I can see doing this is to do four separate crosstabs and then joining them together. There has to be another way.

    How do you all do it?

    Richard

  • April 19, 2006 at 9:34 am

    #633081

    Hi Richard,

    I'm guessing this isn't as easy as...

    select sum(hours), sum(cost), sum(esthours), sum(estcost) from yourview

    ...but I don't see why not from your description of your problem.

     

    Please post some sample data and the result you would like for that sample data...

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • April 19, 2006 at 10:11 am

    #633094

    Sorry about that,

    I want a cross tab report so I have data

    Task    Quarter Hours  Cost EstHours EstCost

    Task 1     Q1      40     4000     40    4000

    Task 1     Q1      30     2500     40    3000

    Task 2     Q1      40     4000     40    4000

    Task 2     Q1      30     2500     40    3000

    Task 1     Q2      40     4000     40    4000

    Task 1     Q2      30     2500     40    3000

    Task 2     Q2      40     4000     40    4000

    Task 2     Q2      30     2500     40    3000

    What I need in the long run is HTML that looks like this

                             Q1                                             Q2

    Task   Hours   Cost   EstHours EstCost   Hours   Cost   EstHours    EstCost

    Task 1   70     6500     80        7000        70      6500      80            7000

    Task 2   70     6500     80        7000        70      6500      80            7000

    Does this make more sense?

    Richard

  • April 19, 2006 at 10:22 am

    #633099

    select  Task

           ,Sum( case when Quarter = 'Q1' then Hours else 0 end) Q1Hours

           ,Sum( case when Quarter = 'Q1' then Cost else 0 end) Q1Cost

           ,Sum( case when Quarter = 'Q1' then EstHours else 0 end) Q1EstHours

           ,Sum( case when Quarter = 'Q1' then EstCost else 0 end) Q1EstCost

           ,Sum( case when Quarter = 'Q2' then Hours else 0 end) Q2Hours

           ,Sum( case when Quarter = 'Q2' then Cost else 0 end) Q2Cost

           ,Sum( case when Quarter = 'Q2' then EstHours else 0 end) Q2EstHours

           ,Sum( case when Quarter = 'Q2' then EstCost else 0 end) Q2EstCost

    -- And so on

    From <Your View>

    group by Task

    -- SQL2005 "Pivot" clause will simplify the expression quite a bit but I am not sure you are using it

     


    * Noel

  • April 19, 2006 at 10:40 am

    #633101

    I will try that out. Unfortunately no, I am not using 2005 yet

    Thanks,

    Richard

  • April 20, 2006 at 12:38 pm

    #633356

    Check out the RAC utility for S2k.It makes both static and dynamic crosstabs with multiple aggregates very simple.Why bother with code?

    http://www.rac4sql.net

     

     

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply