Need help with additional SUM for SQL Pivot

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with additional SUM for SQL Pivot

Author

Topic

direrayne
Starting Member

30 Posts

Posted - 2010-12-10 : 22:35:48

SELECT  *
FROM    ( SELECT    ship ,
                    passengercount ,
                    trxfullprice ,
                    docdate ,
                    vendor
          FROM      dbo.ICL_Sockeye_Flashreport
          WHERE     mediacompany = 'ppi'
                    AND docdate BETWEEN 'november 1 2010'
                                AND     'november 30 2010'
        ) f PIVOT ( SUM(TrxFullPrice) FOR Vendor IN ( hof, jhc ) ) p
ORDER BY ship

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-11 : 00:23:46

[code]
SELECT ship,passengercount,docdate,
SUM(CASE WHEN Vendor ='hof' THEN TrxFullPrice ELSE 0 END) AS hof,
SUM(CASE WHEN Vendor ='hof' THEN TrxQuantity ELSE 0 END) AS hofTrxQuantity,
SUM(CASE WHEN Vendor ='jhc' THEN TrxFullPrice ELSE 0 END) AS jhc,
SUM(CASE WHEN Vendor ='jhc' THEN TrxQuantity ELSE 0 END) AS jhcTrxQuantity
FROM Table
GROUP BY ship,passengercount,docdate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

direrayne
Starting Member

30 Posts

Posted - 2010-12-12 : 09:45:37

Thank you that was the answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources