0

I'm working on an assignment for class and I've been stuck for about two hours on this question:

"Project the invoice average total price, minimum total price, and maximum total price. Money is always formatted to two decimal places."

So, I came up with this query:

SELECT
CONVERT(DECIMAL(8),AVG(INV.TotalPrice),2) AS 'Average Price',
CONVERT(DECIMAL(8),MIN(INV.TotalPrice),2) AS 'Miniumum Price',
CONVERT(DECIMAL(8),MAX(INV.TotalPrice),2) AS 'Maximum Price'
FROM INVOICE INV;

The mathematical functions are working just fine but I'm unable to get to numbers after a decimal point, and the decimal point itself, after the price. For example, on 'Average Price,' I get an output of "187" but I need "187.00."

I'm a little stuck. I've tried looking through the other questions on Stack Overflow but I haven't found one that addresses my specific concerns. Any help would be appreciated.

krebshack
  • 992
  • 1
  • 9
  • 19
  • See also [Write a number with two decimal places SQL server](http://stackoverflow.com/q/441600/161052) – JYelton Aug 06 '11 at 00:56

2 Answers2

2
SELECT
CONVERT(DECIMAL(8,2),AVG(INV.TotalPrice)) AS 'Average Price',
CONVERT(DECIMAL(8,2),MIN(INV.TotalPrice)) AS 'Miniumum Price',
CONVERT(DECIMAL(8,2),MAX(INV.TotalPrice)) AS 'Maximum Price'
FROM INVOICE INV;

The DECIMAL numeric type needs the second argument (scale) to return two decimal places. It looks like you had it almost right, but the argument was passed in the wrong place.

JYelton
  • 35,664
  • 27
  • 132
  • 191
2

The problem is that you are converting to a numerical type. Numerical types represent a quantity, so you should think about them just like you would think about math. 

187 == 187.0 == 187.00000 == 187.0000000000

All of those are exactly the same value, so in a numerical type, they will be represented the same way (usually).

Strings represent the characters that make up a value. Strings can represent text like "This is a string" or "the quick brown dog" or "235136513509835".

In strings:

"187" != "187.00" != "187.0000"

Based on that, I would recommend converting to a string. I don't have a Windows machine so I can't confirm, but try something like:

SELECT
CAST(CONVERT(DECIMAL(8,2),AVG(INV.TotalPrice)) AS varchar(8)) AS 'Average Price',
CAST(CONVERT(DECIMAL(8,2),MIN(INV.TotalPrice)) AS varchar(8)) AS 'Miniumum Price',
CAST(CONVERT(DECIMAL(8,2),MAX(INV.TotalPrice)) AS varchar(8)) AS 'Maximum Price'
FROM INVOICE INV;

Take a look here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Larry
  • 664
  • 6
  • 9