Select Statement with Nested Sum

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)

Select Statement with Nested Sum

Author

Topic

rmrper99
Starting Member

29 Posts

Posted - 2008-10-29 : 12:09:45

I would like a little help with a select statement. I am trying to get a sum of TOTORD in the SOLINE table where SOLINE.INVITD not like '2%' and SOLINE.ORDNBR = SOHEADER.ORDNBR

When I run the statement below, the sum is for the entire SOLINE table, not just for the specific order (which makes sense). However, when I try to run the statement with the piece commented out, I get an error that I need to use an exist statement.

Can someone help me?

SELECT
dbo.SOHEADER.ORDNBR as [Order Number],
dbo.SOHEADER.ORDDATE as [Order Placed Date],
dbo.SOHEADER.USER9 as [Survey Received Date],
dbo.SOHEADER.USER5 as [Est Miles],
dbo.SOHEADER.USER6 as [Est Weight],
sum(dbo.SOLINE.totord)as [Est Move Cost]
--(Select sum(soline.totord), soline.ordnbr from soline where invtid not like '2%')

FROM dbo.SOHEADER INNER JOIN dbo.SOLine ON dbo.SOHEADER.OrdNbr = dbo.SOLine.OrdNbr

WHERE (dbo.SOHEADER.LUpd_DateTime < GETDATE() + 15) AND (dbo.SOHEADER.LUpd_DateTime > GETDATE() - 15) AND dbo.SOHEADER.SOTYPEID in ('DE','IE')

GROUP By dbo.SOHEADER.ORDNBR, dbo.SOHEADER.ORDDATE, dbo.SOHEADER.USER9,
dbo.SOHEADER.USER5, dbo.SOHEADER.USER6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:12:49

quote:
Originally posted by rmrper99

I would like a little help with a select statement. I am trying to get a sum of TOTORD in the SOLINE table where SOLINE.INVITD not like '2%' and SOLINE.ORDNBR = SOHEADER.ORDNBR

When I run the statement below, the sum is for the entire SOLINE table, not just for the specific order (which makes sense). However, when I try to run the statement with the piece commented out, I get an error that I need to use an exist statement.

Can someone help me?

SELECT
dbo.SOHEADER.ORDNBR as [Order Number],
dbo.SOHEADER.ORDDATE as [Order Placed Date],
dbo.SOHEADER.USER9 as [Survey Received Date],
dbo.SOHEADER.USER5 as [Est Miles],
dbo.SOHEADER.USER6 as [Est Weight],
sum(dbo.SOLINE.totord)as [Est Move Cost]
--(Select sum(soline.totord)~~, soline.ordnbr~~ from soline where invtid not like '2%')

FROM dbo.SOHEADER INNER JOIN dbo.SOLine ON dbo.SOHEADER.OrdNbr = dbo.SOLine.OrdNbr

WHERE (dbo.SOHEADER.LUpd_DateTime < GETDATE() + 15) AND (dbo.SOHEADER.LUpd_DateTime > GETDATE() - 15) AND dbo.SOHEADER.SOTYPEID in ('DE','IE')

GROUP By dbo.SOHEADER.ORDNBR, dbo.SOHEADER.ORDDATE, dbo.SOHEADER.USER9,
dbo.SOHEADER.USER5, dbo.SOHEADER.USER6

remove the soline.ordnbr from subquery and try

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 12:17:04

change the where condition in the join to HAVING CLAUSE
like - HAVING SOHEADER.SOTYPEID IN ('DE','IE').
try it out, i dont have a query analyzer here to check it right now.

Regards
Angel

rmrper99
Starting Member

29 Posts

Posted - 2008-10-29 : 12:17:29

If I take that out, then I get the total of SOLINE.TOTORD for the entire table and I need it by ORDNBR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:25:34

[code]SELECT
dbo.SOHEADER.ORDNBR as [Order Number],
dbo.SOHEADER.ORDDATE as [Order Placed Date],
dbo.SOHEADER.USER9 as [Survey Received Date],
dbo.SOHEADER.USER5 as [Est Miles],
dbo.SOHEADER.USER6 as [Est Weight],
sum(dbo.SOLINE.totord)as [Est Move Cost],
s.ordsum

FROM dbo.SOHEADER INNER JOIN dbo.SOLine ON dbo.SOHEADER.OrdNbr = dbo.SOLine.OrdNbr
INNER JOIN (Select sum(totord) as ordsum, ordnbr from soline where invtid not like '2%'
group by ordnbr)s
ON s.ordnbr=dbo.SOLine.OrdNbr

WHERE (dbo.SOHEADER.LUpd_DateTime < GETDATE() + 15) AND (dbo.SOHEADER.LUpd_DateTime > GETDATE() - 15) AND dbo.SOHEADER.SOTYPEID in ('DE','IE')

GROUP By dbo.SOHEADER.ORDNBR, dbo.SOHEADER.ORDDATE, dbo.SOHEADER.USER9,
dbo.SOHEADER.USER5, dbo.SOHEADER.USER6[/code]

rmrper99
Starting Member

29 Posts

Posted - 2008-10-29 : 12:35:46

Thank you visakh16! That was it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:38:01

quote:
Originally posted by rmrper99

Thank you visakh16! That was it!

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources