[Resolved] Need to add up quanties

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)

[Resolved] Need to add up quanties

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-10 : 13:10:35

I need to add up quatities by load number.

The result of the query shows the correct sum for Gross and Net but shows multiple records for each load. I need a single row for each load with quanties added up

select     "load",
           convert(varchar,Date,101),
           branded,
           name,
           sum(cast(gross as decimal(15,2))) OVER (Partition by Load1."load") as GrossTotal,
           sum(cast(net as decimal(15,2))) OVER (Partition by Load1."load") as NetTotal
from       [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded
where      load1.branded = 'ULS'
		   and "load" = 16949

Result
16949 01/21/2009 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 01/21/2009 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 01/21/2009 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 01/21/2009 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00
16949 01/21/2009 ULS DIESEL FUEL - 15 - MV 7528.00 7518.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 13:14:09

DISTINCT?

Or

select     [load],
           convert(char(10), Date, 101),
           branded,
           name,
           cast(sum(gross) as decimal(15, 2)) AS GrossTotal,
           cast(sum(net) as decimal(15,2)) as NetTotal
from       [SFM-TP6000-1].TP6000.dbo.loadcomp as load1
inner join [SFM-TP6000-1].TP6000.dbo.product as prod1 on prod1.product = load1.branded
where      load1.branded = 'ULS'
           and [load] = 16949
GROUP BY   [load],
           convert(char(10), Date, 101),
           branded,
           name

N 56°04'39.26"
E 12°55'05.63"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-10 : 13:21:04

the second suggestion hit the target....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources