Stack Overflow
Stack Overflow

Questions tagged [count]

Count refers to the number of objects in a collection. It's also a commonly-used SQL function that counts the number of rows.

Many programming languages offer some sort of count, length or size fields and/or methods for arrays and collections, e.g. PHP's count($array) or Java's array.length.

COUNT() is also commonly-used SQL function that counts the number of rows in a table. It is an ANSI SQL aggregate function that returns the number of times the argument is encountered per group (or if no non-aggregate columns, per query). Commonly, the argument is specified as * - ie COUNT(*) - simply counting the rows. It can be used to count distinct values of a column like this: COUNT(DISTINCT MY_COLUMN)

Reference

See also:

21460 questions
292
votes
11 answers

How to use count and group by at the same select statement

I have an SQL SELECT query that also uses a GROUP BY, I want to count all the records after the GROUP BY clause filtered the resultset. Is there any way to do this directly with SQL? For example, if I have the table users and want to select the…
Stavros
  • 5,802
  • 13
  • 32
  • 45
269
votes
5 answers

COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?

I often find these three variants: SELECT COUNT(*) FROM Foo; SELECT COUNT(1) FROM Foo; SELECT COUNT(PrimaryKey) FROM Foo; As far as I can see, they all do the same thing, and I find myself using the three in my codebase. However, I don't like to do…
zneak
  • 134,922
  • 42
  • 253
  • 328
268
votes
9 answers

Using a dictionary to count the items in a list

Suppose I have a list of items, like: ['apple', 'red', 'apple', 'red', 'red', 'pear'] I want a dictionary that counts how many times each item appears in the list. So for the list above the result should be: {'apple': 2, 'red': 3, 'pear': 1} How…
Sophie
  • 2,681
  • 2
  • 16
  • 3
250
votes
1 answer

How to get the count of each distinct value in a column?

I have an SQL table called "posts" that looks like this: id | category ----------------------- 1 | 3 2 | 1 3 | 4 4 | 2 5 | 1 6 | 1 7 | 2 Each category number corresponds to a category. How would I go about counting the number of times each…
Jeff Gortmaker
  • 4,607
  • 3
  • 22
  • 29
247
votes
4 answers

SQL query for finding records where count > 1

I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per day with the same account number. UPDATE: Additionally,…
Benjamin Muschko
  • 32,442
  • 9
  • 61
  • 82
246
votes
5 answers

postgresql COUNT(DISTINCT ...) very slow

I have a very simple SQL query: SELECT COUNT(DISTINCT x) FROM table; My table has about 1.5 million rows. This query is running pretty slowly; it takes about 7.5s, compared to SELECT COUNT(x) FROM table; which takes about 435ms. Is there any way…
ferson2020
  • 3,015
  • 3
  • 18
  • 26
243
votes
8 answers

How do we count rows using older versions of Hibernate (~2009)?

For example, if we have a table Books, how would we count total number of book records with hibernate?
craftsman
  • 15,133
  • 17
  • 70
  • 86
242
votes
6 answers

How to count the number of true elements in a NumPy bool array

I have a NumPy array 'boolarr' of boolean type. I want to count the number of elements whose values are True. Is there a NumPy or Python routine dedicated for this task? Or, do I need to iterate over the elements in my script?
norio
  • 3,652
  • 3
  • 25
  • 33
237
votes
7 answers

Fast way to discover the row count of a table in PostgreSQL

I need to know the number of rows in a table to calculate a percentage. If the total count is greater than some predefined constant, I will use the constant value. Otherwise, I will use the actual number of rows. I can use SELECT count(*) FROM…
Renato Dinhani
  • 35,057
  • 55
  • 139
  • 199
232
votes
4 answers

Run a query with a LIMIT/OFFSET and also get the total number of rows

For pagination purposes, I need a run a query with the LIMIT and OFFSET clauses. But I also need a count of the number of rows that would be returned by that query without the LIMIT and OFFSET clauses. I want to run: SELECT * FROM table WHERE /*…
Tim
  • 4,999
  • 3
  • 24
  • 29
232
votes
34 answers

How can I tell how many objects I've stored in an S3 bucket?

Unless I'm missing something, it seems that none of the APIs I've looked at will tell you how many objects are in an /. Is there any way to get a count?
fields
  • 4,433
  • 4
  • 27
  • 32
222
votes
26 answers

How do I get the count of a Swift enum?

How can I determine the number of cases in a Swift enum? (I would like to avoid manually enumerating through all the values, or using the old "enum_count trick" if possible.)
Robert Atkins
  • 23,528
  • 15
  • 68
  • 97
210
votes
16 answers

Fastest way to determine if record exists

As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not. Sample query: SELECT COUNT(*) FROM products WHERE products.id = ?; vs SELECT COUNT(products.id) FROM…
SnakeDoc
  • 13,611
  • 17
  • 65
  • 97
202
votes
10 answers

MySQL - Using COUNT(*) in the WHERE clause

I am trying to accomplish the following in MySQL (see pseudo code) SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) > 10 ORDER BY lastupdated DESC Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a…
Ice
200
votes
14 answers

Count number of records returned by group by

How do I count the number of records returned by a group by query, For eg: select count(*) from temptable group by column_1, column_2, column_3, column_4 Gives me, 1 1 2 I need to count the above records to get 1+1+1 = 3.
Chris
  • 2,923
  • 6
  • 26
  • 25
1
2
3
…
99 100