Tech Off Thread

6 posts

tSql: Grouping by an aggregate function (Msg 8120)

Back to Forum: Tech Off
  • User profile image
    SlackmasterK

    So, I have a table full of information, but every column is a text field. Yeah, I know; move past it.

    I need to do math on some of these text fields (Only the ones that actually contain numbers). Specifically, I need to do sum, avg, and count functions. Is there a way to do it?

    This is the closest I've gotten so far:

    SELECT row_id, [fld0], ahc, convert(varchar,sum([fld1]))
    FROM [db].[dbo].[tbl]
    group by row_id, [fld1]

    Msg 306, Level 16, State 2, Line 1
    The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

    So I tried this:

    SELECT row_id, [fld0], ahc,
    [x] = convert(varchar,[fld1])
    FROM [tbl]
    group by row_id, convert(varchar,[fld1])

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

    Any ideas, short of overhauling the database?

  • User profile image
    Arowin

    Would you not just do it as follows:
    SELECT  [field0],
                [field1]
    ,
                [field2
    ],
                sum(
    convert(int, [field3]))
    FROM    [db].[dbo].[tbl]
    GROUP   BY [field0],
                [field1]
    ,
                [field2
    ]

    Wouldn't you need to convert the text field to some sort of numeric field first - before you do the sum

    Or am I missing something here? Smiley

  • User profile image
    SlackmasterK

    Arowin wrote:
    Would you not just do it as follows:
    SELECT  [field0],
                [field1]
    ,
                [field2
    ],
                sum(
    convert(int, [field3]))
    FROM    [db].[dbo].[tbl]
    GROUP   BY [field0],
                [field1]
    ,
                [field2
    ]

    Wouldn't you need to convert the text field to some sort of numeric field first - before you do the sum

    Or am I missing something here? Smiley


    because:
    Msg 306, Level 16, State 2, Line 1
    The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

    Even varchar would have worked. But it won't let me convert it in the group-by either.

  • User profile image
    harumscarum

    can you just subquery?


    select sum(cast(fld1 as int)) from (
    SELECT row_id, [fld0], ahc, convert(varchar,[fld1])
    FROM [db].[dbo].[tbl]) as tbfoo
    group by row_id, [fld1]

  • User profile image
    Matthew van Eerde

    Cast in your "group by" clause...

    SELECT
        CAST(foo AS int) AS FooInt,
        CAST(bar AS varchar(20)) AS BarVarChar,
        SUM(CAST(baz AS int)) AS SumOfBaz
    FROM
        ...
    GROUP BY
        CAST(foo AS int),
        CAST(bar AS varchar(20))

    Or better, write a VIEW that casts everything, and do all your queries against the VIEW:

    CREATE VIEW Typed AS
    SELECT
        CAST(foo AS int) AS FooInt ,
        CAST(bar AS varchar(20)) AS BarVarChar,
        CAST(baz AS int) AS BazInt
    FROM
        ...

    and then your query is much simpler:

    SELECT
        FooInt,
        BarVarChar,
        SUM(BasInt) AS SumOfBaz
    FROM
        Typed
    GROUP BY
        FooInt,
        BarVarChar

  • User profile image
    SlackmasterK

    Matthew van Eerde wrote:
    CREATE VIEW Typed AS
    SELECT
        CAST(foo AS int) AS FooInt ,
        CAST(bar AS varchar(20)) AS BarVarChar,
        CAST(baz AS int) AS BazInt
    FROM
        ...

    and then your query is much simpler:

    SELECT
        FooInt,
        BarVarChar,
        SUM(BasInt) AS SumOfBaz
    FROM
        Typed
    GROUP BY
        FooInt,
        BarVarChar



    That worked, thanks.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.