Multiple sum query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Multiple sum query

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-02-01 : 12:26:21

Trying to run a query to pull multiple sums (with conflicting criteria).

As designed it obviously does not work as it cancels itself out. Can anyone tweak this to give me an idea of how I should designing these?

From the code ...
my sum RestTotal should be sum for productids 142, 143, 152,153
my sum NonTotal should be sum of all but productids 142,143,152,153
my sum Total is some of all combined.


SELECT     dbo.tblContractDetail.ZoneNo, dbo.tblContractDetail.InHomeDate, SUM(dbo.tblProducts.PagePercent) AS RestTotal, SUM(dbo.tblProducts.PagePercent) 
                      AS NonTotal, SUM(dbo.tblProducts.PagePercent) AS Total
FROM         dbo.tblContractDetail INNER JOIN
                      dbo.tblProducts ON dbo.tblContractDetail.ProductID = dbo.tblProducts.ProductID
WHERE     (dbo.tblContractDetail.InsertType = 'M') AND (dbo.tblContractDetail.FranchiseID = N'mdipho')
GROUP BY dbo.tblContractDetail.ZoneNo, dbo.tblContractDetail.InHomeDate, dbo.tblContractDetail.ProductID
HAVING      (NOT (SUM(dbo.tblProducts.PagePercent) IN (142, 143, 152, 153))) AND (SUM(dbo.tblProducts.PagePercent) IN (142, 143, 152, 153))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:32:18

[code]
SELECT dbo.tblContractDetail.ZoneNo,
dbo.tblContractDetail.InHomeDate,
SUM(CASE WHEN dbo.tblProducts.ProductID IN (142, 143, 152,153) THEN dbo.tblProducts.PagePercent ELSE 0 END) AS RestTotal,
SUM(CASE WHEN dbo.tblProducts.ProductID NOT IN (142, 143, 152,153) THEN dbo.tblProducts.PagePercent ELSE 0 END) AS NonTotal, SUM(dbo.tblProducts.PagePercent) AS Total
FROM dbo.tblContractDetail INNER JOIN
dbo.tblProducts ON dbo.tblContractDetail.ProductID = dbo.tblProducts.ProductID
WHERE (dbo.tblContractDetail.InsertType = 'M') AND (dbo.tblContractDetail.FranchiseID = N'mdipho')
GROUP BY dbo.tblContractDetail.ZoneNo, dbo.tblContractDetail.InHomeDate, dbo.tblContractDetail.ProductID
[/code]

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-02-01 : 12:49:19

Thanks.

Always forget about cases since saving views initially enterprise manager is never smooth.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:55:43

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources