February 7, 2013 at 2:32 pm
hi i would like to understand how to count in sql with different parameters?
for example i have
Date, firstname, name, nationality , mode, origine, id
1/1/2010 teste teste1 fr in p 01
15/07/2005 toto tata lb out L 02
01/03/2012 teste teste1 fr in P 01
i would like to know how many poeple have the most entry in my database
for example here teste teste1 with Id 01 have the most entry it's 2 record
so i need to know the top 100 of poeple who have the most entry
result i need is for example
id 01 = 2 entry
id 02= 1 entry
thanks to help
February 7, 2013 at 2:35 pm
asco5 (2/7/2013)
hi i would like to understand how to count in sql with different parameters?for example i have
Date, firstname, name, nationality , mode, origine, id
1/1/2010 teste teste1 fr in p 01
15/07/2005 toto tata lb out L 02
01/03/2012 teste teste1 fr in P 01
i would like to know how many poeple have the most entry in my database
for example here teste teste1 with Id 01 have the most entry it's 2 record
so i need to know the top 100 of poeple who have the most entry
result i need is for example
id 01 = 2 entry
id 02= 1 entry
thanks to help
select top 100 ID, count(*)
from YourTable
group by ID
Order by count(*)
I think that should be close to what you are looking for.
_______________________________________________________________
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 7, 2013 at 6:16 pm
asco5 (2/7/2013)
so i need to know the top 100 of poeple who have the most entry
How do you want to handle tied counts?
I'm thinking a RANK() or DENSE_RANK() might be more appropriate than TOP 100.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 7, 2013 at 9:30 pm
dwain.c (2/7/2013)
asco5 (2/7/2013)
so i need to know the top 100 of poeple who have the most entryHow do you want to handle tied counts?
I'm thinking a RANK() or DENSE_RANK() might be more appropriate than TOP 100.
I think it should be the Rank() or Dense_Rank()
as asked
i would like to know how many poeple have the most entry in my database
for example here teste teste1 with Id 01 have the most entry it's 2 record
so i need to know the top 100 of poeple who have the most entry
It's for the most entry ; It could be 100 or less than 100 ; by ranking function it could be determined appropriately..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 8, 2013 at 5:07 am
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
February 8, 2013 at 5:16 am
this
select top 100 ID,
count (*)
from [database].
.
group by id
order by count (*);
should be
select top 100 ID,
count (*) myCount
from [database].
.
group by id
order by myCount;
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.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 8, 2013 at 5:35 am
still same probleme not working same error
February 8, 2013 at 5:40 am
select top 100 ID,
count (ID) myCount
from [database].
.
group by id
order by myCount;
You cant use a * in an aggregate query unless you group by every column in the table
select top 100 ID,
count (*) myCount
from [database].
.
group by id, col2, col3, col4, col5......................................
order by myCount;
February 8, 2013 at 5:43 am
still not working
February 8, 2013 at 5:45 am
what is not working? error message? incorrect data outputted? more information please?
February 8, 2013 at 5:50 am
i still have the same error message
this is what i wrote:
select top 100 id, personid,name, firstname,
count (*) mycount
from [database].
.
group by id, personid,name, firstname
order by mycount;
and i have the same error saying:
column is invalid.....
February 8, 2013 at 5:51 am
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) MyCount
FROM
@counting
GROUP BY ID
ORDER BY MyCount DESC
February 8, 2013 at 6:04 am
hi i don't want to insert
i just need to know in my table
who are the id that have the most record depending on their ID and date.
each id is nunique and its for one person
but sometimes this id is repeated depending on the date
so on date 1 i have id 1
on date2 i have id1
on date 3 i have id2
so the id have many record on different times
i need to know how many time i have id1, and id2 etc...
example : id 1 have 45 times recorder
that all
February 8, 2013 at 6:08 am
Well just change the @counting in the select to your table name
The 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.
February 8, 2013 at 7:36 am
Jason-299789 (2/8/2013)
to order by the Count you need to alias it and then reference that in the orderby
anthony.green (2/8/2013)
You cant use a * in an aggregate query unless you group by every column in the table
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.
if object_id('tempdb..#Aggregate') is not null
drop table #Aggregate
create table #Aggregate
(
ID int identity,
SomeValue varchar(50)
)
insert #Aggregate
select top 100 name from sys.objects
select ID, count(*)
from #Aggregate
group by ID
order by count(*)
_______________________________________________________________
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 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply