Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Aggregate function

Posted on 2011-09-20
11
Medium Priority
?
302 Views
Last Modified: 2012-05-12
hi,
i am having an issue when testing this aggregate function.  For instance,
i have 2 columns:
Product and Price
when i wrtie sql like this to show the average price; i get this error "Operand data type nchar is invalid for avg operator"


SELECT Product, avg(Price )as total
FROM dbo.Summary
group by Product
0
Comment
Question by:karinos57
  • 4
  • 4
  • 3
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 36569208
your price is not a numeric type  (even if it looks like it has numeric data in it)

either cast it to a numeric  or change the column itself
0
 

Author Comment

by:karinos57
ID: 36569250
how do i change it?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36569337
open the column list in the object explorer, right click on the price column,  select "Modify",  when the grid appears on the right,  edit the data type to be some numeric type.

close and save the changes.

you will get a warning that some data may be lost if you convert.  If you have a backup then go ahead and continue.
If you don't have a backup, then stop,  take a backup and start over.

Assuming all data in your column is numeric, then it should convert fine.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1000 total points
ID: 36569368
Backing up, Sean, you need to have the table designed correctly, so the HOW is to ALTER TABLE either via T-SQL DDL with same name or throw SSMS via UI. As an alternative, you can take advantage of ISNUMERIC() and CONVERT().

SELECT Product, AVG(CONVERT(MONEY, Price))AS Total
FROM dbo.Summary
WHERE ISNUMERIC(Price) = 1
GROUP BY Product

Couple notes:
- the function on Price in WHERE may hurt performance a bit.
- you can use DECIMAL, NUMERIC, or other appropriate data type instead of MONEY; however, note MONEY will convert a value with '$' whereas DECIMAL will fail.

Kevin
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36569397
*throw == through.
Plus, I did not see Sean's previous note when I started typing. That is the SSMS UI method. There is a setting in Options > Designers > Table and Database Designers > "Prevent saving changes that require table re-creation" that may need to be disabled to get this to work in that method, but give it a shot. Aside from disabling that setting, you can use ALTER TABLE ALTER COLUMN as stated, although, it is probably best to add a new column, convert the valid data elements and default others appropriately. You can then remove the original column ...
0
 

Author Comment

by:karinos57
ID: 36569474
thnx.  I used this and it works great now.  but how can i round the numbers? i get too many decimals:
use DSR
SELECT ScriptName, AVG(CAST(KPI_Score AS numeric(12,2)))KPI_Score
FROM dbo.KPI_Summary
group by ScriptName
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36569511
ROUND(..., 2) is what you would use to round an numerical value to two decimal places.
0
 

Author Comment

by:karinos57
ID: 36569539
where shoud i add the Round 2?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36569557
depends,  do you want the average of the rounded values?

AVG(round(CAST(KPI_Score AS numeric(12,2)),2))

or do you want the rounded value of the average?

round(AVG(CAST(KPI_Score AS numeric(12,2))),2)

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36569584
Yes, I am sorry. I am popping online on breaks in action of my training and so my postings have been sparse on words. :) Sean's explanation is the reason I showed on its own as you can place that where ever you want the rounding to occur.
0
 

Author Closing Comment

by:karinos57
ID: 36575451
tx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question