SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Operand data type varchar is invalid for sum opera
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

muhie
Starting Member

South Africa
3 Posts

Posted - 09/22/2011 :  05:31:22  Show Profile  Reply with Quote

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

USA
5072 Posts

Posted - 09/22/2011 :  06:01:53  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

muhie
Starting Member

South Africa
3 Posts

Posted - 09/22/2011 :  06:06:00  Show Profile  Reply with Quote
whats the DDL?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 09/22/2011 :  06:15:42  Show Profile  Visit russell's Homepage  Reply with Quote
Table definition.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/22/2011 :  12:14:30  Show Profile  Reply with Quote
SUM('HLitre') would itself cause the error. it should be SUM(HLitre)

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000