Post reply

aggregate functions group by

  • March 5, 2015 at 10:51 am

    #301721

    hoping someone can help. I have to create a query that will display average price average quantity and product sales for an order. Product sales is a calculated field UnitPrice*Quantity*(1-discount) there should only be one row. i tried it this way but its not one row of data

    SELECT AVG(UnitPrice) AS [Average Unit Price], AVG(Quantity) AS [Average Quantity], (UnitPrice * Quantity) * (1 - Discount) AS [Product Sales]

    FROM dbo.[Order Details]

    GROUP BY (UnitPrice * Quantity) * (1 - Discount)

  • March 5, 2015 at 10:59 am

    #1781989

    I suppose this is what you want:

    SELECT AVG(UnitPrice) AS [Average Unit Price], AVG(Quantity) AS [Average Quantity], SUM((UnitPrice * Quantity) * (1 - Discount)) AS [Product Sales]

    FROM dbo.[Order Details]

    GROUP BY (UnitPrice * Quantity) * (1 - Discount)

    -- Gianluca Sartori

  • March 5, 2015 at 11:14 am

    #1781997

    thanks, its almost what i was looking for but i need all input to be accross one row ill like the first example

    and not like second

  • March 5, 2015 at 11:17 am

    #1782000

    My bad, I forgot to delete the last line

    SELECT AVG(UnitPrice) AS [Average Unit Price], AVG(Quantity) AS [Average Quantity], SUM((UnitPrice * Quantity) * (1 - Discount)) AS [Product Sales]

    FROM dbo.[Order Details]

    Better?

    -- Gianluca Sartori

  • March 5, 2015 at 11:31 am

    #1782006

    thank you so much you saved my life lol

  • March 5, 2015 at 11:41 am

    #1782012

    Glad I could help

    -- Gianluca Sartori

Viewing 6 posts - 1 through 5 (of 5 total)

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