[Resolved] Query does not total a single record

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] Query does not total a single record

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-23 : 10:56:46

I have a query that does not render totals the way I need:

select     l.[load] as Bol#,
           convert(char(10), l.date, 101) as Date,
           p.petroexcode as PetroEXCode,
           l.branded as Product,
           substring(p.name,1,30) as Name,
           cast(sum(l.gross) as decimal(15, 2)) AS GrossTotal,
           cast(sum(l.net) as decimal(15,2)) as NetTotal
from       [SFM-TP6000-1].TP6000.dbo.product as p
inner join [SFM-TP6000-1].TP6000.dbo.loadcomp as l on l.branded = p.product
where      (@Product IS null or p.petroexcode = @Product) and
           (@DateFrom IS Null or l.date >= @DateFrom) and
           (@DateTo IS Null or l.date <= DATEADD(DAY, 1, @DateTo))
group by   l.[load],
           l.date,
           p.petroexcode,
           l.branded,
           p.name

Result:
20848 07/01/2009 V96 ULS DY DIESEL FUEL - 15 DYED - NRLM 3001.00 2972.00
20848 07/01/2009 V96 ULS DY DIESEL FUEL - 15 DYED - NRLM 3001.00 2970.00
20849 07/01/2009 V96 ULS DY DIESEL FUEL - 15 DYED - NRLM 4000.00 3958.00

What I need is one line with totals added up.

20848 07/01/2009 V96 ULS DY DIESEL FUEL - 15 DYED - NRLM 6001.00 5942.00
20849 07/01/2009 V96 ULS DY DIESEL FUEL - 15 DYED - NRLM 4000.00 3958.00

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 11:02:11

[code]select l.[load] as Bol#,
convert(char(10), l.date, 101) as Date,
p.petroexcode as PetroEXCode,
l.branded as Product,
substring(p.name,1,30) as Name,
cast(sum(l.gross) as decimal(15, 2)) AS GrossTotal,
cast(sum(l.net) as decimal(15,2)) as NetTotal
from [SFM-TP6000-1].TP6000.dbo.product as p
inner join [SFM-TP6000-1].TP6000.dbo.loadcomp as l on l.branded = p.product
where (@Product IS null or p.petroexcode = @Product) and
(@DateFrom IS Null or l.date >= @DateFrom) and
(@DateTo IS Null or l.date <= DATEADD(DAY, 1, @DateTo))
group by l.[load],
convert(char(10), l.date, 101),
p.petroexcode,
l.branded,
p.name

[/code]

Madhivanan

Failing to plan is Planning to fail

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-23 : 11:07:01

Worked, thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources