Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    6

    Unanswered: Text data type and "count" in a select statement

    I have two tables, connected with ActionID-fkActionID

    tblAction
    ActionID [int]
    Description [text]

    tblActionOccurrence
    ID [int]
    fkActionID [int]

    I want to select everything from tblAction, plus the number of occurrences of each Action in tblActionOccurrence, but following select string doesn't work, because text type can't be used in a GROUP BY clause.:

    select ActionID, Description, count(fkActionID) as ActionCount'
    from tblAction left join tblActionOccurrence on ActionID = fkActionID
    group by ActionID, Description

    Is there any other way to write the select statement?

    I'm using Microsoft SQL Server

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Re: Text data type and "count" in a select statement

    Text as a field type is considered a BLOB, unless you are storing a very large description look into using a varchar type instead. Just make sure and define the field size large enough to contain your largest description. Varchar fields should work fine with your code.


    Originally posted by hugoj79
    I have two tables, connected with ActionID-fkActionID

    tblAction
    ActionID [int]
    Description [text]

    tblActionOccurrence
    ID [int]
    fkActionID [int]

    I want to select everything from tblAction, plus the number of occurrences of each Action in tblActionOccurrence, but following select string doesn't work, because text type can't be used in a GROUP BY clause.:

    select ActionID, Description, count(fkActionID) as ActionCount'
    from tblAction left join tblActionOccurrence on ActionID = fkActionID
    group by ActionID, Description

    Is there any other way to write the select statement?

    I'm using Microsoft SQL Server

  3. #3
    Join Date
    Feb 2003
    Posts
    6
    Ok. I think Varchar will work too. Thank you!

Posting Permissions

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