Need Help with Simple Query 2 - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Need Help with Simple Query 2

Author

Topic

ross_gt
Starting Member

23 Posts

Posted - 2011-05-27 : 18:43:40

I keep getting this message:
Operand data type nvarchar is invalid for sum operator.

USE Customer_Analytics

SELECT [Country of Ultimate Destination Code], [Order Date (YYYYMMDD)], SUM([Extended Sales PMAR Amount]) AS Revenue
FROM [CleanData].[0607080910_sorted]
WHERE [Order Date (YYYYMMDD)] >= '20110101'
AND [Country of Ultimate Destination Code] LIKE N'%CA%';

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 19:48:36

The error message is saying that you cannot sum up character columns. For summation, the column has to be a numeric data type such as int, float etc. If the data in [Extended Sales PMAR Amount] is really numeric, you would be able to make the error message go away by casting it as a numeric type. For example:


SELECT [Country of Ultimate Destination Code], [Order Date (YYYYMMDD)], SUM(cast([Extended Sales PMAR Amount] as float)) AS Revenue
FROM [CleanData].[0607080910_sorted]
WHERE [Order Date (YYYYMMDD)] >= '20110101' 
AND [Country of Ultimate Destination Code] LIKE N'%CA%';

HOWEVER, if you are able to, you should change the data type of the [Extended Sales PMAR Amount] column to a numeric type. Not only will that avoid the problems such as the one you are seeing now, it would also avoid other problems in the future. For example, if someone, even accidentally inserted non-numeric data into that column (which they can if it is nvarchar), then even your modified query that has the cast as I described above would fail, because casting to float will fail.

ross_gt
Starting Member

23 Posts

Posted - 2011-05-27 : 19:54:13

I understand what you are saying and I made the change; however, I now receive this error after I make your change:

Column 'CleanData.0607080910_sorted.Country of Ultimate Destination Code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 20:08:18

Of course, sorry I overlooked that. You also need a group by clause. In general, the rule is that anything that is in the select list, but is not inside of an aggregate function (such as sum, count etc.) must be in the group by clause. So you will need to change it to:


SELECT [Country of Ultimate Destination Code], [Order Date (YYYYMMDD)], SUM(cast([Extended Sales PMAR Amount] as float)) AS Revenue
FROM [CleanData].[0607080910_sorted]
WHERE [Order Date (YYYYMMDD)] >= '20110101' 
AND [Country of Ultimate Destination Code] LIKE N'%CA%'
GROUP BY
   [Country of Ultimate Destination Code], [Order Date (YYYYMMDD)];

ross_gt
Starting Member

23 Posts

Posted - 2011-05-27 : 20:49:28

Awesome! That did the trick! Thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources