SQL count(*)

  • I would like to select top 20 rows along with count of number of rows it is selecting from a table. The following SQL (MS SQL 2000) statement gives me errors. What am I doing wrong here?

    SELECT TOP 20 Id, Title, Body,

    CategoryID, DatePublished, PostedBy, CommentCount, searchkeywords, isenabled, count(ID) as BlogCount

    FROM BlogEntry WHERE isEnabled=1

    GROUP BY DatePublished DESC

    ERRORS:

    Server: Msg 8118, Level 16, State 1, Line 1

    Column 'BlogEntry.CommentCount' is invalid in the select list because it is

    not contained in an aggregate function and there is no GROUP BY clause.

    Server: Msg 8118, Level 16, State 1, Line 1

    Column 'BlogEntry.searchkeywords' is invalid in the select list because it is

    not contained in an aggregate function and there is no GROUP BY clause.

    Server: Msg 8118, Level 16, State 1, Line 1

    Column 'BlogEntry.isenabled' is invalid in the select list because it is

    not contained in an aggregate function and there is no GROUP BY clause.

  • When you use an aggregate function, all non-aggregate data must be included in the GROUP BY.

    Greg

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

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