InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 14:58:24 GMT20http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]asco5 (2/8/2013)[/b][hr][quote][b]Sean Lange (2/8/2013)[/b][hr][quote][b]asco5 (2/8/2013)[/b][hr]thanks for the code its seem to works i just need to test it in a bigger database with more entryi want to ask if i have a lots of sql requesti would like to put them in one aplication a simple application with buttonso when i will click on the button it will display the resulti guess i have to link my application with my databasedo you a tutorial how to do a sql application, instead of everytime working directly in the databasethanks[/quote]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.[/quote]hiyes but what do you mean by stored procedure?do you have an example of application that i can complete with more sql queries?thanks[/quote]This should help answer your first question.[url=http://msdn.microsoft.com/en-us/library/ms187926.aspx]http://msdn.microsoft.com/en-us/library/ms187926.aspx[/url]I am not sure what you mean about "an example of application that i can complete with more sql queries"?Fri, 08 Feb 2013 09:52:33 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]Sean Lange (2/8/2013)[/b][hr][quote][b]asco5 (2/8/2013)[/b][hr]thanks for the code its seem to works i just need to test it in a bigger database with more entryi want to ask if i have a lots of sql requesti would like to put them in one aplication a simple application with buttonso when i will click on the button it will display the resulti guess i have to link my application with my databasedo you a tutorial how to do a sql application, instead of everytime working directly in the databasethanks[/quote]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.[/quote]hiyes but what do you mean by stored procedure?do you have an example of application that i can complete with more sql queries?thanksFri, 08 Feb 2013 09:33:23 GMTasco5http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]asco5 (2/8/2013)[/b][hr]thanks for the code its seem to works i just need to test it in a bigger database with more entryi want to ask if i have a lots of sql requesti would like to put them in one aplication a simple application with buttonso when i will click on the button it will display the resulti guess i have to link my application with my databasedo you a tutorial how to do a sql application, instead of everytime working directly in the databasethanks[/quote]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.Fri, 08 Feb 2013 09:27:52 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxthanks for the code its seem to works i just need to test it in a bigger database with more entryi want to ask if i have a lots of sql requesti would like to put them in one aplication a simple application with buttonso when i will click on the button it will display the resulti guess i have to link my application with my databasedo you a tutorial how to do a sql application, instead of everytime working directly in the databasethanksFri, 08 Feb 2013 08:57:18 GMTasco5http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]Sean Lange (2/8/2013)[/b][hr][quote][b]anthony.green (2/8/2013)[/b][hr]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.[/quote]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.[/quote]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.Fri, 08 Feb 2013 08:11:57 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]anthony.green (2/8/2013)[/b][hr]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.[/quote]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.Fri, 08 Feb 2013 07:52:42 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]asco5 (2/8/2013)[/b][hr]hi i tried first oneselect top 100 cause i need only the 100 first people who have the most entryso i didselect top 100 ID, count (*)from [database].[table].[table]group by idorder by count (*);i received an errorcolumn [database].[table].[table] is invalid in the select list because it is not contained in either aggregate function or the group by clausethanks for heping[/quote]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.[code="SQL"]select top 100 ID, count (1) AS cntfrom [database].[schema].[table]group by idorder by cnt DESC;[/code]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.Fri, 08 Feb 2013 07:48:36 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxWell 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.Fri, 08 Feb 2013 07:42:19 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]Jason-299789 (2/8/2013)[/b][hr]to order by the Count you need to alias it and then reference that in the orderby[/quote][quote][b]anthony.green (2/8/2013)[/b][hr]You cant use a * in an aggregate query unless you group by every column in the table[/quote]I hate to disagree with both of you but you are both incorrect on this. You do not have to name the aggregate to sort it and you don't have to group by every column when using count.[code]if object_id('tempdb..#Aggregate') is not null drop table #Aggregate create table #Aggregate( ID int identity, SomeValue varchar(50))insert #Aggregateselect top 100 name from sys.objectsselect ID, count(*)from #Aggregategroup by IDorder by count(*)[/code]Fri, 08 Feb 2013 07:36:27 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxWell just change the @counting in the select to your table nameThe table variable and insert is to create a testing environment using readily consumable data which someone can just pick up and generate a solution. It is part of the forum etiquette when posting T-SQL questions to provide this information. Take a look in the second link in my signature, and it should explain more as to why I have done that bit of code.Fri, 08 Feb 2013 06:08:07 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxhi i don't want to inserti just need to know in my tablewho are the id that have the most record depending on their ID and date.each id is nunique and its for one personbut sometimes this id is repeated depending on the dateso on date 1 i have id 1on date2 i have id1on date 3 i have id2so the id have many record on different timesi need to know how many time i have id1, and id2 etc...example : id 1 have 45 times recorderthat allFri, 08 Feb 2013 06:04:34 GMTasco5http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[code="sql"]declare @counting table ([date] date, firstname char(10), name char(10), nationality char(2), mode char(3), origine char(1), ID char(2))INSERT INTO @counting values('2010-01-01','teste','teste1','fr','in','p','01'),('2005-07-15','toto','tata','lb','out','L','02'),('2012-03-01','teste','teste1','fr','in','P','01')SELECT ID, COUNT(ID) MyCountFROM @countingGROUP BY IDORDER BY MyCount DESC[/code]Fri, 08 Feb 2013 05:51:36 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxi still have the same error messagethis is what i wrote:select top 100 id, personid,name, firstname,count (*) mycountfrom [database].[table].[table]group by id, personid,name, firstnameorder by mycount;and i have the same error saying:column is invalid.....Fri, 08 Feb 2013 05:50:17 GMTasco5http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxwhat is not working? error message? incorrect data outputted? more information please?Fri, 08 Feb 2013 05:45:53 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxstill not workingFri, 08 Feb 2013 05:43:49 GMTasco5http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[code="sql"]select top 100 ID, count (ID) myCountfrom [database].[table].[table]group by idorder by myCount;[/code]You cant use a * in an aggregate query unless you group by every column in the table[code="sql"]select top 100 ID, count (*) myCountfrom [database].[table].[table]group by id, col2, col3, col4, col5......................................order by myCount;[/code]Fri, 08 Feb 2013 05:40:18 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxstill same probleme not working same errorFri, 08 Feb 2013 05:35:19 GMTasco5http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxthis [code="sql"]select top 100 ID, count (*)from [database].[table].[table]group by idorder by count (*);[/code]should be [code="sql"]select top 100 ID, count (*) myCountfrom [database].[table].[table]group by idorder by myCount;[/code]to order by the Count you need to alias it and then reference that in the orderby, dont use Ordinal positions as its a pain to workout column 42 of a 100 column select, even worse when someone adds a 2 or more new columns a head of it.Besides I understand that sorting by the ordinal is going to be depreciated in a later version.Fri, 08 Feb 2013 05:16:56 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxhi i tried first oneselect top 100 cause i need only the 100 first people who have the most entryso i didselect top 100 ID, count (*)from [database].[table].[table]group by idorder by count (*);i received an errorcolumn [database].[table].[table] is invalid in the select list because it is not contained in either aggregate function or the group by clausethanks for hepingFri, 08 Feb 2013 05:07:49 GMTasco5http://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]dwain.c (2/7/2013)[/b][hr][quote][b]asco5 (2/7/2013)[/b][hr]so i need to know the top 100 of poeple who have the most entry[/quote]How do you want to handle tied counts?I'm thinking a RANK() or DENSE_RANK() might be more appropriate than TOP 100.[/quote]I think it should be the Rank() or Dense_Rank()as asked[quote]i would like to know how many poeple have the most entry in my databasefor example here teste teste1 with Id 01 have the most entry it's 2 recordso i need to know the top 100 of poeple who have the most entry[/quote]It's for the most entry ; It could be 100 or less than 100 ; by ranking function it could be determined appropriately..Thu, 07 Feb 2013 21:30:09 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]asco5 (2/7/2013)[/b][hr]so i need to know the top 100 of poeple who have the most entry[/quote]How do you want to handle tied counts?I'm thinking a RANK() or DENSE_RANK() might be more appropriate than TOP 100.Thu, 07 Feb 2013 18:16:05 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspx[quote][b]asco5 (2/7/2013)[/b][hr]hi i would like to understand how to count in sql with different parameters? for example i haveDate, firstname, name, nationality , mode, origine, id 1/1/2010 teste teste1 fr in p 0115/07/2005 toto tata lb out L 0201/03/2012 teste teste1 fr in P 01i would like to know how many poeple have the most entry in my databasefor example here teste teste1 with Id 01 have the most entry it's 2 recordso i need to know the top 100 of poeple who have the most entryresult i need is for exampleid 01 = 2 entryid 02= 1 entrythanks to help[/quote]select top 100 ID, count(*)from YourTablegroup by IDOrder by count(*)I think that should be close to what you are looking for.Thu, 07 Feb 2013 14:35:19 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1417359-391-1.aspxhi i would like to understand how to count in sql with different parameters? for example i haveDate, firstname, name, nationality , mode, origine, id 1/1/2010 teste teste1 fr in p 0115/07/2005 toto tata lb out L 0201/03/2012 teste teste1 fr in P 01i would like to know how many poeple have the most entry in my databasefor example here teste teste1 with Id 01 have the most entry it's 2 recordso i need to know the top 100 of poeple who have the most entryresult i need is for exampleid 01 = 2 entryid 02= 1 entrythanks to helpThu, 07 Feb 2013 14:32:01 GMTasco5