Double Sum in Pivot

  • hrvvoda

    SSC Enthusiast

    Points: 114

    I'm using this select to get pivot for column Prices. It's fine, but I also want to do sum for columns M1,M2, and M3,M4.

    SELECT *,25 M1Cijena, 25 M2Cijena, 16 M3Cijena, 16 M4Cijena
    FROM ( 
           select u.pin Pin,u.firstname Name,u.lastname,sum(tmt.Price) Prices, tmt.type MealType 
           from TA_Meals tm 
           left outer join TA_MealsType tmt  on tm.MealType = tmt.id   
           full outer join users u  on u.pin = tm.pin 
           where u.department = 1000001001  group by u.pin,u.firstname,u.lastName,tmt.Type
    ) as s
    PIVOT( 
            SUM(Prices)  FOR MealType IN (M1,M2,M3,M4) **<-- here also want sum for M1,M2 and M3,M4.**
    )AS pvt

  • Luis Cazares

    SSC Guru

    Points: 183637

    hrvvoda - Friday, March 29, 2019 1:55 AM

    I'm using this select to get pivot for column Prices. It's fine, but I also want to do sum for columns M1,M2, and M3,M4.

    SELECT *,25 M1Cijena, 25 M2Cijena, 16 M3Cijena, 16 M4Cijena
    FROM ( 
           select u.pin Pin,u.firstname Name,u.lastname,sum(tmt.Price) Prices, tmt.type MealType 
           from TA_Meals tm 
           left outer join TA_MealsType tmt  on tm.MealType = tmt.id   
           full outer join users u  on u.pin = tm.pin 
           where u.department = 1000001001  group by u.pin,u.firstname,u.lastName,tmt.Type
    ) as s
    PIVOT( 
            SUM(Prices)  FOR MealType IN (M1,M2,M3,M4) **<-- here also want sum for M1,M2 and M3,M4.**
    )AS pvt

    Welcome to the forums!
    We'll be glad to help you out, but we need more information to do so. Please post sample data, and expected results based on that sample data. To know the correct way to post it, check the articles in my signature.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • David Burrows

    SSC Guru

    Points: 64814


    SELECT *, M1+M2, M3+M4, 25 M1Cijena, 25 M2Cijena, 16 M3Cijena, 16 M4Cijena
    FROM ...

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 3 posts - 1 through 3 (of 3 total)

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