Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to count in sql?


how to count in sql?

Author
Message
asco5
asco5
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 30
i still have the same error message

this is what i wrote:

select top 100 id, personid,name, firstname,
count (*) mycount
from [database].[table].[table]
group by id, personid,name, firstname
order by mycount;

and i have the same error saying:
column is invalid.....
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6102 Visits: 6078


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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


asco5
asco5
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 30
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
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6102 Visits: 6078
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6102 Visits: 6078
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


DiverKas
DiverKas
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 460
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].[table].[table]
group by id
order by count (*);

i received an error
column [database].[table].[table] 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].[table]
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
asco5
asco5
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 30
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search