dcsimg
Results 1 to 3 of 3

Thread: Adding a calculated column in SQL Server

  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Adding a calculated column in SQL Server

    I have the following query:

    SELECT
    HPP.Period,
    HPP.Clik,
    SUM (HPP.New_Adds) AS HPP_GA_Count,
    SUM (HPP.HPP_New_Adds) AS HPP_Adds_Count,

    CASE WHEN HPP_New_Adds = 0 THEN 0
    ELSE round(sum(HPP.HPP_New_Adds) / cast(sum(HPP.New_Adds) AS FLOAT),4) END AS [HPP_%]

    FROM dbo.tbl_HPP_Monthly AS HPP

    GROUP BY
    Period,
    Clik,

    CASE WHEN HPP_New_Adds = 0 THEN 0
    ELSE round(sum(HPP.HPP_New_Adds) / cast(sum(HPP.New_Adds) AS FLOAT),4) END

    When attempting to run I get this message:

    Msg 144, Level 15, State 1, Line 17
    Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

    I'm trying to add a new column that calculates the percentage of two columns....here are the columns... the result should be 30.30%.

    Period Clik HPP_GA_Count HPP_Adds_Count
    10/1/2012 1029 33 10


    Thank you in advance for any help I can get!

    -O

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,912
    As the message says you can't use aggregate in group by. Since you are using aggregate in SELECT, does the result make sense if you simply group by period and clik only?

  3. #3
    Join Date
    Dec 2012
    Posts
    2
    Quote Originally Posted by skhanal View Post
    As the message says you can't use aggregate in group by. Since you are using aggregate in SELECT, does the result make sense if you simply group by period and clik only?


    First, Thanks for your response....much appreciated. Eliminating the case at the group by will be ideal scenario but it does not work either.... Here is the message it gives:

    Msg 8120, Level 16, State 1, Line 7
    Column 'dbo.tbl_HPP_Monthly.HPP_New_Adds' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •