Solved

Aggregate function

Posted on 2011-09-20
11
295 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 250 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 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 59

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 59

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 59

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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