We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [count]

COUNT : an aggregate SQL function that is used to count the number of rows.

Filter by
Sorted by
Tagged with
58
votes
4answers
36k views

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 ...
37
votes
1answer
40k views

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 ...
24
votes
2answers
40k views

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 ...
22
votes
3answers
18k views

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 ...
20
votes
6answers
4k views

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 ...
19
votes
3answers
30k views

PostgreSQL using count() to determine percentages (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 ...
19
votes
3answers
7k views

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 ...
18
votes
5answers
25k views

MySQL IS NULL / IS NOT NULL Misbehaving?

Please have look at this table: mysql> desc s_p; +-------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | ...
17
votes
1answer
66k views

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 ...
14
votes
2answers
17k views

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 ...
14
votes
1answer
11k views

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, ...
13
votes
1answer
4k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
12
votes
2answers
10k views

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 ...
12
votes
1answer
8k views

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?
11
votes
1answer
7k views

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 ...
11
votes
2answers
66k views

Select only those records that have different/multiple values for a particular column

Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values: Member table ID ...
10
votes
5answers
2k views

SQL counting distinct over partition

I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A. MyTable Col_A | Col_B A | 1 A | 1 A | 2 A | 2 A | 2 A | 3 b | ...
9
votes
3answers
58k views

How to make multiple counts in one query?

I count records with queries like SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%' SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%' SELECT COUNT(col1) FROM table1 WHERE col1 ...
9
votes
4answers
14k views

What can speed up a SQL count query?

When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems? I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so I'...
9
votes
4answers
14k views

show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table. In other words, can you think of a query to come up with something like this in mysql? +---------------------...
9
votes
1answer
531 views

Why SELECT COUNT() query execution plan includes left joined table?

In SQL Server 2012 I have table valued function with join to another table I need to to count number of rows for this 'table valued function'. When I inspect the execution plan, I can see the left ...
8
votes
3answers
6k views

Speeding up Count(*) on large tables

We're using a Vendor App running on SQL Server Enterprise, and it has a rather annoying quirk of executing COUNT statements on the Items table while processing most financial documents (orders, ...
8
votes
2answers
757 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
8
votes
3answers
35k views

Count null and not null values in a column

How to count and retrieve null and not null on same column in MySQL? mytable --------------------------------------------------- id | name | visited | registDate | ------------------...
8
votes
3answers
23k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
8
votes
1answer
38k views

What does COUNT(1) actually count? [duplicate]

Possible Duplicate: What is the difference between select count(*) and select count(any_non_null_column)? I have heard it commonly stated that when counting the number of rows in a query, you ...
7
votes
1answer
14k views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
7
votes
4answers
10k views

MySQL COUNT(*) performance

I have a table with more than 15m rows. I need the total number of rows. So: SELECT COUNT(*) FROM thetable; Which takes around 50s to complete. Explaining gives me Select tables optimized away. I ...
6
votes
2answers
923 views

Help with this query

I have a person table. I also have a car table and books table. The car and book tables both have a foreign key back to person. I am trying to count the number of books and cars each person has. I ...
6
votes
4answers
5k views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on '...
6
votes
2answers
2k views

Including count of a result in the main query?

Currently we execute two queries to get the count and its result with a pagination filter. While we can easily combine both of these into a single network call, is there anyway to do this in a single ...
6
votes
3answers
292 views

How to count the number of rows by datetime field with the division into time intervals

I'm using Oracle Database 10g. There is a table with information about clients: +----+------+---------------------+ | id | name | registered_at | +----+------+---------------------+ | 1 | ...
5
votes
2answers
5k views

COUNT(*) gives more than 1 with LIMIT 1?

I'm trying to count old records. Why does Postgres give a result of 1160, even though I set some limit, LIMIT 1 in this case? SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT ...
5
votes
3answers
176 views

Oracle and MS-SQL-like row counts

My applications often need an accurate but perhaps imperfect row count. Billion-row tables often get frequent writes, so "perfect" is a matter of definition and timing. Microsoft SQL keeps very good ...
5
votes
2answers
24k views

Distinct Combination of Two Columns

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other. The data is all around backup policies being run on against ...
5
votes
3answers
2k views

MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed

I benchmark DBs to find out the best for my project and I found that count(*) is extremely slow in PostgeSQL. And I don't understand is it a normal behaviour of PostgeSQL or I do something wrong. I ...
5
votes
1answer
322 views

I was interrupted while transferring a very large table. How can I pick it up from where it stopped?

I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the ...
5
votes
2answers
6k views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
5
votes
1answer
858 views

Fastest query for distinct IDs in a many-to-many relationship

I have this table in PostgreSQL 9.4: CREATE TABLE user_operations( id SERIAL PRIMARY KEY, operation_id integer, user_id integer ) The table consists of ~1000-2000 different operations ...
4
votes
2answers
7k views

Count with where clause

Is there a way to have WHERE clause in COUNT ? Or how to count with some clause ? select a b, count(c where t1.u='UAE') as c1 from t1 I am using MS SQL Server 2014
4
votes
1answer
24k views

count multiple occurences of attribute values for each row in sql

I have a mytable structured as follows and I would like to count occurences of values for attribute in each row: id | attribute -------------- 1 | spam 2 | egg 3 | spam With SELECT id, attribute,...
4
votes
2answers
40k views

Join two tables and return data and count in a single query

I have two tables in an SQL Server database, one with two columns and one with four: tbl_email_list email_list_id int (PK) email_list_name varchar tbl_email-details email_uniq_id int (PK) ...
4
votes
2answers
27k views

Is SELECT COUNT GROUP BY more efficient than counting a result set?

I have a table with a rank column which can be either 1, 2 or 3. I need to count the number of records for each rank, but I also need to display all the data ordered by rank. From my perspective, the ...
4
votes
3answers
4k views

Join Multiple Tables for Aggregates

How do I join multiple tables for aggregates with Sum and Count functions? The query I'm trying is as below: Select campaigns.id, campaigns.name, Count(landers.campaign_id) As landers_count, ...
4
votes
2answers
345 views

Why does SAMPLE BLOCK often return zero?

In a comment on another question, someone asked: I used SAMPLE BLOCK in many tests but found that it would inexplicably return '0' much of the time. Any idea why? I speculated that this might be ...
4
votes
2answers
704 views

How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function). But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy ...
4
votes
1answer
972 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items attached ...
4
votes
1answer
1k views

Get the would-be row count without running twice

So I have a stored proc that utilizes a paging function to only return a subset of the query along with the actual total row count. In other words, it takes parameters for the start and end rows to ...
4
votes
2answers
1k views

How to get COUNT of a range between 2 columns

Table: Staff | StartingTime | EndingTime | starthour | endhour ------------------------------------------------------------- 10 | 0813 | 1610 | 08 | 16 20 | ...
3
votes
6answers
37k views

Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so, SELECT MAX('Row') FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS) Error Desc: Incorrect syntax near ')'....