Get 2 decimals in my query

  • 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)

  • 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)

  • 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.

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

  • 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 3 years, 9 months ago by  szkat.
  • 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)

  • 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

  • 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 😉

    😎

  • 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)

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

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