COUNT : an aggregate SQL function that is used to count the number of rows.
COUNT() is an aggregate SQL function that returns the number of times the argument is encountered per group (or if there are no non-aggregate columns, per query).
Questions tagged [count]
449 questions
127
votes
1 answer
Postgres Count with different condition on the same query
EDIT Postgres 9.3
I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2
The current query is working fine which looks like this:
Basically it is a 3 table inner join. I did not make this query but the developer…
jackhammer013
- 1,469
- 2
- 12
- 11
62
votes
4 answers
What is the difference between select count(*) and select count(any_non_null_column)?
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.
What are the differences between these two statements, if any?
Martin
- 2,420
- 4
- 26
- 35
38
votes
2 answers
MySQL - Difference between using count(*) and information_schema.tables for counting rows
I want a fast way to count the number of rows in my table that has several million rows. I found the post "MySQL: Fastest way to count number of rows" on Stack Overflow, which looked like it would solve my problem. Bayuah provided this…
Programster
- 533
- 1
- 4
- 10
37
votes
2 answers
Improve performance of COUNT/GROUP-BY in large PostgresSQL table?
I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how many nodes I do the following (added explain…
tomka
- 967
- 1
- 10
- 16
35
votes
1 answer
MySQL Count rows from another table for each record in table
SELECT
student.StudentID,
student.`Name`,
COUNT(attendance.AttendanceID) AS Total
FROM
student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
I am trying to count the last row but instead it counts all the results and…
Ali Shaikh
- 479
- 1
- 6
- 11
31
votes
2 answers
Why is count(*) slow, when explain knows the answer?
This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I can see a column in the output with the number of…
Benubird
- 495
- 1
- 5
- 8
25
votes
3 answers
How to select specific rows if a column exists or all rows if a column doesn't
I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this in one query?
Eg:
Table users has a column called…
Matt S.
- 353
- 1
- 3
- 6
23
votes
3 answers
Determine percentage from count() without cast issues
I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0.
select (count (refinst) / (select count(*) from patients) * 100) as "Formula"
from…
user3779117
- 331
- 1
- 2
- 4
23
votes
6 answers
How to use COUNT with multiple columns?
How to use multiple columns with a single COUNT?
Assume that there is a table demo with these data:
id | col1 | col2 |
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
1 |'alice' | 'book1'|
2 |'bob' | 'book1'|
3 |'alice' | 'book2'|
4 …
andy
- 351
- 1
- 2
- 7
21
votes
3 answers
Why doesn't InnoDB store the row count?
Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row insertions/deletions while that query executes.
As I…
Radu Murzea
- 408
- 3
- 11
20
votes
6 answers
Count where two or more columns in a row are over a certain value [basketball, double double, triple double]
I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating the statistics I wanted, but now I've run into a…
user39509
19
votes
5 answers
MySQL IS NULL / IS NOT NULL Misbehaving?
Please have look at this table:
mysql> desc s_p;
+-------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra …
user1213259
- 221
- 1
- 2
- 6
17
votes
1 answer
PostgreSQL: COUNT(*) uses a sequential scan, not index
Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?
Adam Matan
- 12,079
- 30
- 82
- 96
17
votes
1 answer
Why is this sqlite query much slower when I index the columns?
I have a sqlite database with two tables, each with 50,000 rows in, containing names of (fake) people. I've constructed a simple query to find out how many names there are (given name, middle initial, surname) that are common to both tables:
select…
chiastic-security
- 273
- 1
- 2
- 7
16
votes
1 answer
Postgres: count(*) vs count(id)
I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).
My question is about Postgres' internal optimizations. Is it smart…
ldrg
- 709
- 2
- 8
- 16