40
votes
Accepted
MySQL - Difference between using count(*) and information_schema.tables for counting rows
There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable ...
- 97.4k
29
votes
Accepted
Why is count(*) slow, when explain knows the answer?
Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block.
Here's a cheap way to get an estimated row count:
SELECT table_rows
FROM ...
- 598
22
votes
Accepted
Why is this sqlite query much slower when I index the columns?
In SQLite, joins are executed as nested loop joins, i.e., the database goes through one table, and for each row, searches matching rows from the other table.
If there is an index, the database can ...
- 5,183
22
votes
Accepted
SQL counting distinct over partition
This is how I'd do it:
SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
FROM #MyTable AS mt2
WHERE mt2.Col_A = mt....
- 38.8k
19
votes
Accepted
COUNT(*) gives more than 1 with LIMIT 1?
You're limiting the resultset of the aggregate function count(), which will always return 1 row. IE: It's limiting the output of the count(*) function, rather than LIMITing just FROM data WHERE ...
- 31.6k
17
votes
How to use COUNT with multiple columns?
There are several things you can count with COUNT() function:
count(*) : rows
count(col1) : rows where col1 is not null
count(col2) : rows where col2 is not null
count(distinct col1) : distinct col1 ...
- 97.4k
14
votes
Count null and not null values in a column
Try
SELECT
DATE_FORMAT(registDate, '%m-%Y') AS month,
COUNT(name) AS register,
SUM(!ISNULL(visited)) AS visited,
SUM(ISNULL(visited)) AS not_visited
FROM mytable
GROUP BY DATE_FORMAT(...
- 241
14
votes
Accepted
Speeding up Count(*) on large tables
The indexed view should be among the fastest options, with the lowest maintenance overhead, when implemented optimally.
Modifications are incremental (deltas) as I explain in detail in Indexed View ...
- 83k
13
votes
Accepted
Carry over long sequence of missing values with Postgres
I would form groups with the window function count() and then take the first value for each group:
SELECT foo_label
, first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) ...
- 172k
12
votes
Accepted
Why SELECT COUNT() query execution plan includes left joined table?
If ForeignId, ForeignTable, IsMain is not known* to be unique in ExternFile, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected....
- 16.2k
11
votes
Count with where clause
No, the syntax you have is not valid, it can be corrected by the use of a CASE expression.
(and I guess you have a GROUP BY a, b as you'd get an error otherwise).
select
a
b,
count(case when ...
- 97.4k
10
votes
Postgresql extremely slow count (with index, simple query)
Fetching and counting 5 million rows is slow business.
There are two problems:
The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with ...
- 47.9k
9
votes
Accepted
Get count estimates from pg_class.reltuples for given conditions
Not out of the box. But you can achieve it with a ...
Partial index
CREATE INDEX tbl_name_hello_idx ON tbl(tbl_id) WHERE name LIKE 'hello%';
SELECT reltuples
FROM pg_class
WHERE oid = '...
- 172k
9
votes
Accepted
count multiple occurences of attribute values for each row in sql
select
m1.id,
m1.attribute,
(select count(*) from mytable m2 where m2.attribute = m1.attribute)
from
mytable m1
;
Another version:
select
m1.id,
m1.attribute,
m2.c
from
mytable m1
...
- 40.4k
9
votes
Accepted
Can PostgreSQL use indexes to expedite count(distinct) queries?
There is a separate entry in a B-tree index for every row, for duplicates, too. So we can never "simply count the number of rows in the index". There are as many index rows to read and ...
- 172k
9
votes
Accepted
SQL Return Table with where clause and occurrences of value of total table
You can use a windowed COUNT inside a derived table (subquery)
SELECT *
FROM (
SELECT *,
Total = COUNT(*) OVER (PARTITION BY t.RouteId)
FROM MyTable t
) t
WHERE t.Name = 'Bob';
db<&...
- 11.8k
8
votes
MySQL COUNT(*) performance
Back when mysql was not transactionally sound by default (when people regularly used myISAM tables instead of InnoDB because that was the default or, going further back in time, because it didn't ...
- 32k
8
votes
Accepted
Speeding up a GROUP BY, HAVING COUNT query
You want the 10 most common values for "groupingsFrameHash" with their respective counts (excluding unique values) - a common task. This specification caught my attention, though:
a fuzzy ...
- 172k
8
votes
Accepted
How do you count the occurrences of an anchored string using PostgreSQL?
You can solve this with a
FASTEST was the pl/perl method which I placed last on this list because it requires pl/perl, and is likely not needed for most workloads.
FAST A string function, such as one ...
- 62.2k
8
votes
Accepted
Select unique value pairs that occur (count) more than once inside a table
What you have almost works, just remove the distinct and change the > 2 to > 1. The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three ...
- 23.8k
8
votes
Accepted
Counting unique (distinct) users per day
Because your form is off, what you want is
SELECT count(DISTINCT x)
FROM generate_series(1,10) AS gs(x);
Or, in your case,
SELECT start_time::date, count(DISTINCT user_id)
FROM mytable
GROUP BY ...
- 62.2k
8
votes
Count items on condition
Use the modern aggregate FILTER syntax in Postgres 9.4 or later:
SELECT userid,
COUNT(*) FILTER (WHERE version_identifier LIKE '%ios%') AS nr_ios,
COUNT(*) FILTER (WHERE ...
- 172k
8
votes
Accepted
Simultaneous aggregate count and full count
What you want is ROLLUP:
SELECT COUNT(DISTINCT correlation_id), type
FROM events
WHERE type IN (3, 5, 97, 98)
GROUP BY ROLLUP (type);
- 25.2k
7
votes
Get count based on 15 minutes of interval
This is a classic example of how a "Numbers table" can really help get the results you need.
Essentially, you create a table containing the 15 minute increments you desire, then join your table to ...
- 69.7k
7
votes
UPDATE only if COUNT from another table is not zero
Derive a table of counts by id from table2, join the results to table1 and use that join in the UPDATE statement:
UPDATE
table1 AS t1
INNER JOIN
(
SELECT
id,
COUNT(*) AS ...
- 22.8k
7
votes
How to use COUNT with multiple columns?
Obviously, COUNT(DISTINCT) with multiple columns counts unique combinations of the specified columns' values. However, one other important point is that a tuple is counted only if none of the ...
- 22.8k
7
votes
SQL counting distinct over partition
You can emulate it by using dense_rank, and then pick the maximum rank for each partition:
select col_a, col_b, max(rnk) over (partition by col_a)
from (
select col_a, col_b
, dense_rank()...
7
votes
SQL counting distinct over partition
This is, in a way, an extension to Lennart's solution, but it is so ugly that I dare not suggest it as an edit. The goal here is to get the results without a derived table. There may never be the need ...
- 22.8k
6
votes
Accepted
Count per week per column PostgreSQL
As count() counts only the non-NULL values, this is a simple GROUP BY query:
SELECT week, count(col1), ..., count(col10)
FROM your_table
GROUP BY week;
- 31k
6
votes
Join Multiple Tables for Aggregates
This request is quite old, but as the accepted answer is wrong, I thought I'd add a correct one, so future readers don't get too confused.
A campain has landers and conversions. If we merely join all ...
- 193
Only top scored, non community-wiki answers of a minimum length are eligible