Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

how to count in sql? Expand / Collapse
Author
Message
Posted Friday, February 8, 2013 5:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 10:10 AM
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.....
Post #1417641
Posted Friday, February 8, 2013 5:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 5,132, Visits: 4,922


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

Post #1417642
Posted Friday, February 8, 2013 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 10:10 AM
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
Post #1417650
Posted Friday, February 8, 2013 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 5,132, Visits: 4,922
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
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

Post #1417653
Posted Friday, February 8, 2013 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
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)
Post #1417705
Posted Friday, February 8, 2013 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 5,132, Visits: 4,922
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
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

Post #1417708
Posted Friday, February 8, 2013 7:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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.
Post #1417709
Posted Friday, February 8, 2013 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 13,067, Visits: 11,904
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)
Post #1417711
Posted Friday, February 8, 2013 8:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
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.



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)
Post #1417731
Posted Friday, February 8, 2013 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 10:10 AM
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
Post #1417760
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse