Problems with GROUP BY

13 Aug 2005 4:00 AM

Marco Napoli

I have the below SELECT statement and I am having a problem:

SELECT status, SUM(NoItems) * Price AS NewRevenue
FROM orders
GROUP BY status, Price

The problem is because I need to place the Price field in the GROUP BY I
receive multiple rows. I need the result to be on one row. Any ideas on what
I am doing wrong?

Thank you

Peace in Christ
Marco Napoli
http://www.ourlovingmother.org

13 Aug 2005 4:10 AM

Dan Guzman

Is this what you're looking for?

    SELECT status, SUM(NoItems * Price) AS NewRevenue
    FROM orders
    GROUP BY status

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Marco Napoli" <marco@avantitecnospam.com> wrote in message
news:uoBFPu7nFHA.1412@TK2MSFTNGP09.phx.gbl...
>I have the below SELECT statement and I am having a problem:
>
> SELECT status, SUM(NoItems) * Price AS NewRevenue
> FROM orders
> GROUP BY status, Price
>
> The problem is because I need to place the Price field in the GROUP BY I
> receive multiple rows. I need the result to be on one row. Any ideas on
> what I am doing wrong?
>
> Thank you
>
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>
>