Operand data type varchar is invalid for sum opera

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

General SQL Server Forums

Script Library

Operand data type varchar is invalid for sum opera

Author

Topic

muhie
Starting Member

3 Posts

Posted - 2011-09-22 : 05:31:22

SELECT SalesArea, Branch, 'Region', 'Town', 'RepCode', 'Rep', 'Customer', ReportRep, 'Segment', Brand, Pack, 'Calyear', 'CalMonth', InvoiceDate, TradeDay,
'DayName', SUM (QtyInvoiced) AS QtyInvoiced, SUM('HLitre') AS HLitre
FROM (SELECT br.SalesArea, br.Branch, dbo.Proper(ac.RegionClass2) AS 'Region', dbo.Proper(ac.Location) AS 'Town', ac.Salesperson AS 'RepCode',
dbo.Proper(td.Name) AS 'Rep', dbo.Proper(ac.Name) AS 'Customer', td.ReportRep, dbo.Proper(ac.MicroSegment) AS 'Segment', ds.Brand,
ds.Pack, DATEPART(yy, a.InvoiceDate) AS 'Calyear', DATEPART(mm, a.InvoiceDate) AS 'CalMonth', a.InvoiceDate, b.TradeDay,
DATENAME(dw, a.InvoiceDate) AS 'DayName', a.QtyInvoiced, a.Volume AS 'HLitre'
FROM dbo.vArTrnDetail AS a WITH (nolock) INNER JOIN
dbo.Customers AS ac WITH (nolock) ON a.Customer = ac.Customer INNER JOIN
dbo.vBranch AS br WITH (nolock) ON ac.Branch = br.Branch INNER JOIN
dbo.DimStockCode AS ds WITH (nolock) ON a.StockCode = ds.StockCode LEFT OUTER JOIN
dbo.vSalesBudget AS b WITH (nolock) ON b.Area = ac.SalesTeam AND a.InvoiceDate = b.TradeDate LEFT OUTER JOIN
dbo.SalesReps AS td ON ac.Salesperson = td.Salesperson AND ac.Branch = td.Branch
UNION ALL
SELECT 'Distell' AS 'SalesArea', 'ZZ' AS 'Branch', dc.Region, dc.Town, dc.Rep AS 'RepCode', dc.Rep, dc.CustName AS 'Customer', b.ReportRep,
dc.Segment, ISNULL(ds.Brand, 'Not Captured') AS 'Brand', ISNULL(ds.Pack, 'Not Captured') AS 'Pack', a.CalYear, a.CalMonth, a.InvoiceDate,
td.TradeDay, DATENAME(dw, a.InvoiceDate) AS 'DayName', a.Cases, a.HLitres AS 'HL'
FROM dbo.DistellSales AS a INNER JOIN
dbo.DistellCustomers AS dc ON a.Customer = dc.CustCode INNER JOIN
dbo.DistellReps AS b ON dc.Rep = b.Rep LEFT OUTER JOIN
dbo.DimStockCode AS ds ON a.StockCode = ds.StockCode LEFT OUTER JOIN
(SELECT DISTINCT TradeDate, LEFT(DATENAME(dw, TradeDate), 3) AS 'dayName', TradeDay
FROM dbo.SalesBudget AS td) AS td ON a.InvoiceDate = td.TradeDate
WHERE (a.HLitres <> 0)) AS a
GROUP BY SalesArea, Branch, Region, Town, RepCode, Rep, Customer, ReportRep, Segment, Brand, Pack, Calyear, CalMonth, InvoiceDate, TradeDay,DayName

Error i get is?

Msg 8117, Level 16, State 1, Line 3
Operand data type varchar is invalid for sum operator.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 06:01:53

Without DDL can't say exactly, but one of the fields you're summing is a varchar. You'll need to cast it to a numeric data type before summing it. If you have non-numerics in there, then the cast will fail too. May need to do some scrubbing there.

Also, should remove the single quotes from your aliases.

muhie
Starting Member

3 Posts

Posted - 2011-09-22 : 06:06:00

whats the DDL?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 06:15:42

Table definition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 12:14:30

SUM('HLitre') would itself cause the error. it should be SUM(HLitre)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources