Home Forums SQL Server 2008 SQL Server 2008 - General how to count in sql? RE: how to count in sql?

  • February 8, 2013 at 7:48 am

    #1585964

    asco5 (2/8/2013)

    hi i tried first one

    select top 100 cause i need only the 100 first people who have the most entry

    so i did

    select top 100 ID,

    count (*)

    from [database].

    .

    group by id

    order by count (*);

    i received an error

    column [database].

    .

    is invalid in the select list because it is not contained

    in either aggregate function or the group by clause

    thanks for heping

    The REAL issue is, the FROM clause: Database.table.table is NOT correct. To be pedantic it should be SERVER.DATABASE.SCHEMA.TABLE, but it is generally sufficient to leave off the server part.

    select top 100 ID,

    count (1) AS cnt

    from [database].[schema].

    group by id

    order by cnt DESC;

    That is what I would use, based on the requirements I have seen presented. Rank and DenseRank probably would give better results, but I am not sure if the stated requirements need it.