Post reply

SUM of a single column in SELECT

  • November 11, 2011 at 10:05 am

    #150672

    Hi,

    Does anyone know how can I get the SUM of a single column within a SELECT statement ? The problem is I dont want the aggregate for the other columns in the SELECT.

    When I try to SUM just the one column, I get this error:

    Column 'MG_BOOKING.BOOKING_NUM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Any suggestions would be appreciated.

    Thanks,

    Paul

  • November 11, 2011 at 10:32 am

    #1406494

    Personally I don’t like doing things like that, but it can be done with the over clause. The code bellow is based on AdventureWorks database:

    select ProductID, SUM(ActualCost) from Production.TransactionHistory group by ProductID

    select ProductID, SUM(ActualCost) over (partition by null) from Production.TransactionHistory

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 14, 2011 at 2:44 am

    #1407005

    Adi Cohn-120898 (11/11/2011)

    Personally I don’t like doing things like that, but it can be done with the over clause. The code bellow is based on AdventureWorks database:

    select ProductID, SUM(ActualCost) from Production.TransactionHistory group by ProductID

    select ProductID, SUM(ActualCost) over (partition by null) from Production.TransactionHistory

    Adi

    Thanks very much for your reply. I really appreciate it.

Viewing 3 posts - 1 through 2 (of 2 total)

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