Get 2 decimals in my query

Question

Get 2 decimals in my query

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

andycadley - Wednesday, July 25, 2018 12:34 PM
DDL will definitely help people to help you. You might want to investigate the FORMAT function which can probably help you get where you're trying to go, eg:
Select Format(3.456,'0.00'), Format(0.2,'0.00'), Format(0.2,'#.00')

Carefull now... The FORMAT function takes 44 times longer to execute than CONVERT does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

GG_BI_GG - Tuesday, July 24, 2018 12:50 AM
SELECT 'Per Category' AS Category,
[1], [2], [3], [4], [5], [6], [7], [8]
FROM
(SELECT CategoryID, AVG(UnitPrice) as UnitPrice
FROM Products
GROUP BY CategoryID) AS SourceTable
PIVOT
(
AVG(UnitPrice) FOR CategoryID IN ([1], [2], [3], [4], [5], [6], [7], [8] )
) AS PivotTable;
The results need to look like this:
Per Category 1 2 3 4 5 6 7 8
Average Unit Price 37.98 23.06 25.16 28.73 20.25 54.01 32.37 20.68

Getting back to this original question, use the CONVERT function to convert the output columns in your SELECT list to DECIMAL with 2 decimal places. DO understand what rounding will do to you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

Jeff Moden - Saturday, July 28, 2018 5:46 AM
GG_BI_GG - Tuesday, July 24, 2018 12:50 AM
SELECT 'Per Category' AS Category,
[1], [2], [3], [4], [5], [6], [7], [8]
FROM
(SELECT CategoryID, AVG(UnitPrice) as UnitPrice
FROM Products
GROUP BY CategoryID) AS SourceTable
PIVOT
(
AVG(UnitPrice) FOR CategoryID IN ([1], [2], [3], [4], [5], [6], [7], [8] )
) AS PivotTable;
The results need to look like this:
Per Category 1 2 3 4 5 6 7 8
Average Unit Price 37.98 23.06 25.16 28.73 20.25 54.01 32.37 20.68
Getting back to this original question, use the CONVERT function to convert the output columns in your SELECT list to DECIMAL with 2 decimal places. DO understand what rounding will do to you.

Yes back to the original question, I'd like to see what results the user is getting. Maybe he has his numbers stored as integers. An average of integers will return an integer. Maybe its just rounding.

----------------------------------------------------

szkat Grasshopper Points: 11 More actions · Answer 4

DataCamp accepts it like this however yours is correct too:

SELECT 'Average Unit Price' AS [Per Category],
[1], [2], [3], [4], [5], [6], [7], [8]
FROM
(SELECT CategoryID, AVG(UnitPrice) as UnitPrice
FROM Products
GROUP BY CategoryID) AS SourceTable
PIVOT
(
AVG(PIVOT.UnitPrice) FOR PIVOT.CategoryID IN ([1], [2], [3], [4], [5], [6], [7], [8] )
) AS PivotTable;

This reply was modified 4 years, 5 months ago by szkat.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Thom A wrote:

saravanatn - Tuesday, July 24, 2018 2:02 AM
GG_BI_GG - Tuesday, July 24, 2018 12:58 AM
Product Table look like in the Picture.
I think Erikur mentioned clearly what needs to be done .Kindly post at least minimum information to answer your question.
Have a look at the link in my signature on how to respond Eirikur's request. 🙂

Not seeing a link in your signature line anymore, Thom.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99129 More actions · Answer 6

Jeff Moden wrote:

Thom A wrote:
saravanatn - Tuesday, July 24, 2018 2:02 AM
GG_BI_GG - Tuesday, July 24, 2018 12:58 AM
Product Table look like in the Picture.
I think Erikur mentioned clearly what needs to be done .Kindly post at least minimum information to answer your question.
Have a look at the link in my signature on how to respond Eirikur's request. 🙂
Not seeing a link in your signature line anymore, Thom.

Pretty sure the "great" forum upgrade borked it 😉

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Thom A wrote:

Jeff Moden wrote:
Thom A wrote:
saravanatn - Tuesday, July 24, 2018 2:02 AM
GG_BI_GG - Tuesday, July 24, 2018 12:58 AM
Product Table look like in the Picture.
I think Erikur mentioned clearly what needs to be done .Kindly post at least minimum information to answer your question.
Have a look at the link in my signature on how to respond Eirikur's request. 🙂
Not seeing a link in your signature line anymore, Thom.
Pretty sure the "great" forum upgrade borked it 😉

The link was there in Tom's response, before the forum's upgrademic 😉

😎

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

I sure am happy to see that I'm not the only one that thinks the contributors that seriously helped make this forum great have taken it in the socks in one way or another on every "upgrade" they've ever made.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)