Post reply

how to count in sql?

  • February 8, 2013 at 7:42 am

    #1585963

    Well you learn something new every day.

    That was the way I was tought back when I was a young whipper snapper, so I guess it just stuck.

  • 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.

  • February 8, 2013 at 7:52 am

    #1585966

    anthony.green (2/8/2013)

    Well you learn something new every day.

    That was the way I was tought back when I was a young whipper snapper, so I guess it just stuck.

    Count is one of those funky aggregates. It counts the number of rows regardless of what column, or even a constant, you use as your count. There has long been a myth that counting * is slower then counting 1 but it just isn't true. I have also heard the myth that count(NullableColumn) will ignore nulls in count, this is also not true.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 8, 2013 at 8:11 am

    #1585984

    Sean Lange (2/8/2013)

    anthony.green (2/8/2013)

    Well you learn something new every day.

    That was the way I was tought back when I was a young whipper snapper, so I guess it just stuck.

    Count is one of those funky aggregates. It counts the number of rows regardless of what column, or even a constant, you use as your count. There has long been a myth that counting * is slower then counting 1 but it just isn't true. I have also heard the myth that count(NullableColumn) will ignore nulls in count, this is also not true.

    Further clarification: COUNT(DISTINCT colname) will count the number of distinct nonnull values in the specified column. Perhaps this is where the myth of excluding nulls comes from.

  • February 8, 2013 at 8:57 am

    #1586011

    thanks for the code its seem to works i just need to test it in a bigger database with more entry

    i want to ask if i have a lots of sql request

    i would like to put them in one aplication a simple application with button

    so when i will click on the button it will display the result

    i guess i have to link my application with my database

    do you a tutorial how to do a sql application, instead of everytime working directly in the database

    thanks

  • February 8, 2013 at 9:27 am

    #1586031

    asco5 (2/8/2013)

    thanks for the code its seem to works i just need to test it in a bigger database with more entry

    i want to ask if i have a lots of sql request

    i would like to put them in one aplication a simple application with button

    so when i will click on the button it will display the result

    i guess i have to link my application with my database

    do you a tutorial how to do a sql application, instead of everytime working directly in the database

    thanks

    Create a stored procedure to query your database, then have your application call that stored procedure. That is the best way to do this sort of thing.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 8, 2013 at 9:33 am

    #1586037

    Sean Lange (2/8/2013)

    asco5 (2/8/2013)

    thanks for the code its seem to works i just need to test it in a bigger database with more entry

    i want to ask if i have a lots of sql request

    i would like to put them in one aplication a simple application with button

    so when i will click on the button it will display the result

    i guess i have to link my application with my database

    do you a tutorial how to do a sql application, instead of everytime working directly in the database

    thanks

    Create a stored procedure to query your database, then have your application call that stored procedure. That is the best way to do this sort of thing.

    hi

    yes but what do you mean by stored procedure?

    do you have an example of application that i can complete with more sql queries?

    thanks

  • February 8, 2013 at 9:52 am

    #1586049

    asco5 (2/8/2013)

    Sean Lange (2/8/2013)

    asco5 (2/8/2013)

    thanks for the code its seem to works i just need to test it in a bigger database with more entry

    i want to ask if i have a lots of sql request

    i would like to put them in one aplication a simple application with button

    so when i will click on the button it will display the result

    i guess i have to link my application with my database

    do you a tutorial how to do a sql application, instead of everytime working directly in the database

    thanks

    Create a stored procedure to query your database, then have your application call that stored procedure. That is the best way to do this sort of thing.

    hi

    yes but what do you mean by stored procedure?

    do you have an example of application that i can complete with more sql queries?

    thanks

    This should help answer your first question.

    http://msdn.microsoft.com/en-us/library/ms187926.aspx

    I am not sure what you mean about "an example of application that i can complete with more sql queries"?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 16 through 22 (of 22 total)

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