Questions tagged [count]

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

Filter by
Sorted by
Tagged with
-1
votes
2answers
155 views

Refining Productivity & Efficiency Query

I'm trying to write a query that is, in effect, a Productivity and Efficiency Report for measuring the statistics of those of us (such as myself) who type up the paper work orders that our mechanics ...
1
vote
4answers
114 views

Query slows with additional FROM item (even with no extra actions)

I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms: SELECT COUNT(body) FROM comment WHERE body LIKE '%[deleted]%'; ...
1
vote
3answers
2k views

Is COUNT(DISTINCT PRIMARY_KEY) faster than COUNT(*)?

I haven't got a particular problem here, I'm just interested. One of my associates uses COUNT(DISTINCT(PRIMARY_KEY)) every time he needs to select all the data from a table (with 100m + rows) is it ...
1
vote
1answer
136 views

Efficient count query for consecutive time windows

I have a SQL Server Database: in one table, one of the columns represents the time of an event (of type datetime2(7)). Another column represents a product ID. My goal is to specify a flexible time ...
1
vote
1answer
663 views

Mysql Count(*) incorrect value

Alrighty so I'm trying to setup a procedure to do a count of locations returned from a geosearch. Geo-search for a specific area returns 2640 records (with a limit of 5000, and offset of 0) but the ...
2
votes
2answers
709 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
3
votes
2answers
8k views

How do you count the occurrences of an anchored string using PostgreSQL?

If I have a string in a column on a row in a table like this 1 2 2 2 2 2 2 How would I count the occurrence of a substring 2 inside the string. Assume nothing other than a space-delimiter of " ". ...
4
votes
2answers
432 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 ...
5
votes
3answers
192 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 ...
1
vote
2answers
3k views

How to select random row with count()

Table names: g_number name 123 John 123 Sara 123 Michael I want to get one RANDOM name and quantity of all names with the same g_number in one query: SELECT name, count(*) cnt ...
1
vote
1answer
32 views

Sum of different table counts

I have 2 different query and i want to summarize their count in other query like this: select a,((select count(id) from foo) + (select count(id) from bar)) as 'alpha' from beta; Is there a ...
0
votes
1answer
111 views

Get count of relationship rows between three tables that are not duplicates

I have a (local) MySQL database, which is a Drupal 6 export. It has these relevant tables: node - Contains articles, plus also information about their department and sections. I'm interested in nid ...
0
votes
2answers
119 views

Getting count(column) as well as a specific row in each group

I am working with SQL Server 2012. I want to get the count of column as well as the rows with specific condition in each group. The query I have in mind looks something like this: select count(...
0
votes
2answers
5k views

How can I get distinct counts with different where clauses on the same field? [closed]

How can I get DISTINCT row COUNTs with different conditions in a single SELECT statement, and if count is zero get zero as a result? I tried the following query: SELECT DISTINCT A.DeviceFilter, ...
5
votes
1answer
3k views

Including count of a result in the main query?

Currently we execute two queries to get the count and result with a pagination filter. While we can easily combine both of these into a single network call (using sql seprators), is there a way to do ...
1
vote
1answer
801 views

row count incorrectly displayed as 0 in redshift

I have a table in redshift with columns user_id and some_property, neither of which are unique. The following query returns 0: SELECT COUNT(DISTINCT user_id) FROM table WHERE user_id NOT IN (SELECT ...
0
votes
1answer
70 views

Entries count by date is giving wrong results

I am getting some strange results with below queries: SELECT count(id) FROM `asins` WHERE `StockUpdated` >= '2016-12-30' and `StockUpdated` <= '2016-12-31' The result is : 24446 SELECT count(...
-1
votes
1answer
34 views

How to count records generated by following query?

How can I get the total count of records generated by following query? SELECT AccountA.Name AS 'Account_Name', AccountA.Industry AS 'Account_Industry', AccountA.Key_Account__c AS 'Key_Account', UserA....
4
votes
2answers
8k 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
1
vote
1answer
3k views

Using DISTINCT and COUNT in a query

I'm having issues using DISTINCT and COUNT in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.) ...
0
votes
1answer
129 views

How can I count the students on mongodb

I would like to display the list of students who have passed at least 3 courses at ENSA with more than 60. db.etudiants.insert({ nom: 'emile', prenom: 'josé', cours_reussis: [{ ...
1
vote
2answers
36 views

Identifying values that are not existent in other table

I've got two tables that have one to many associations on a pmid. So if one table has an pmid, the second table should have multiple rows with the same pmid. However, something went sideways and I'm ...
1
vote
1answer
48 views

Postgres - how to avoid unnecessary comparisons inside a WHERE with a JOIN?

Background We're running this query: SELECT COUNT(*) AS "__count" FROM "xfiler_document" INNER JOIN "xfiler_tx" ON ("xfiler_document"."tx_id" = "xfiler_tx"."id") WHERE ( "xfiler_document"."...
0
votes
2answers
2k views

Super slow MariaDB 5.5 count(*) on large simple table

I'm doing some testing on a dedicated mediocre server (Core 2 2G ram). Before I upgrade to 8G ram, I am concerned something is very wrong here. Simple queries have normal performance: MariaDB [...
6
votes
3answers
553 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 | ...
2
votes
1answer
3k views

how to sort by timestamp field while counting duplicates

I have a table with username, timestamp and action columns. I need to retrieve a list of usernames, total occurrences of each user and the latest timestamp for each user. The following statement works ...
1
vote
3answers
84 views

How to add a summary count to this query

I am using Postgres 9.4 and I have a query that looks like the following: SELECT attendees.event_id, attendees.user_id, users.tags FROM users, attendees WHERE 'crystal:promo' = ANY(users.tags) AND ...
0
votes
1answer
174 views

Limit the number of results but show how much would be returned

I have a big problem in T-SQL with large amount of data where I need to limit number of returned rows to 200K but also to show the number of rows that would be returned. It is needed to be done under ...
2
votes
2answers
13k views

Aggregate interval data to hourly

I am getting data for 15 minutes interval. I would like to aggregate this data to hourly.Can anybody advise how should i do this query to sum the interval values and display it?
4
votes
1answer
3k views

Speed up count queries on a couple million rows

Assuming a db full of products. A product can belong to exactly 1 collection and is created by a user. Rough scale of the db: Products: 52.000.000 Collections: 9.000.000 Users: roughly 9.000.000 as ...
1
vote
2answers
50 views

Two separate column counts grouped by one of those columns

I have a Voters table, which has fields including party and email. party takes just a few acceptable values. I have a query which counts party membership and groups by the party: SELECT party as ...
3
votes
2answers
2k views

count null values of table from information_schema tables

Is there any way to count null values in a column by using information_Schema tables? EG: SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='myschema' and ...
17
votes
2answers
23k 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 ...
3
votes
1answer
1k views

Getting duplicate results in many to many query

I have designed a bookstore app with these tables. There's a book with an M2M relation to author. Lastly, there's table reader which keeps track of which user has read the book i.e. an entry in the ...
15
votes
1answer
13k 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, ...
0
votes
1answer
40 views

Mysql query to get count result and group by

I've got three tables with different records for users, User username | realname | date evn-az-3ju john 11/2012 03:09:40 p.m. jwyvm_rdyt steve 12/2012 ...
1
vote
1answer
766 views

Select count question with where clause

I have a initial table where I have the following rows: ID_0, geom and utilisatio (where the value varies from 1 to 7). I am trying to create a table where I would have the count of a specific ...
6
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 ...
0
votes
1answer
3k views

Select countries and counts of people per country

I have 2 tables, one is a list of countries (id, country) and another is the users (where the country column is the id of the country in the countries table). Is it possible to make a single query ...
3
votes
2answers
239 views

T-SQL Count in single select

I have a table called Catalog. It has multiple columns, for example CreatedByID and ModifiedByID that contain user IDs. I would like to get count of how many documents a user has created and edited. ...
0
votes
1answer
128 views

SELECT records based on return of COUNT query

I would like to create a query that returns records if values in a column occur more than once. I came across other Stack Exchange questions but these only dealt with returning the number of ...
1
vote
1answer
35 views

Find addresses shared by more than one customer_id where customer_name is is distinct

I need to return the list of addresses that have more than one customer_ID associated with them, exclude the customer_IDs that have the same customer_name and return the number of time the address ...
0
votes
2answers
233 views

How to improve query count execution with mySql replicate?

For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table). And its really time consuming when it ...
1
vote
1answer
20 views

Selecting entries with the same count as in another table

I'm using a SELECT statement to select multiple IDs and than use GROUP BY ID HAVING COUNT(*) = someNumber to only have the IDs with a certain count. What I however actually want to do is to select ...
0
votes
1answer
98 views

Group COUNT by month less than variable

In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus: CREATE TABLE [dbo].[Employee] ( [employeeID] [int] IDENTITY(9500,1) NOT NULL, [firstName] [nvarchar](50) ...
1
vote
3answers
368 views

COUNTING results per row in self referenced table

I have a table called Clients. And at this table, I have an idFather to know who added this row to the table. Sample: id | user | idFather | createdAt ---+---------+----------+----------- 01 | ...
2
votes
1answer
78 views

How to UPDATE by COUNT from JOINed tables

I have a schema as CREATE TABLE companies ( company_id int(11) NOT NULL AUTO_INCREMENT, company varchar(255), projects int(11), PRIMARY KEY(company_id) ) ENGINE=InnoDB CREATE TABLE employees ( ...
2
votes
3answers
607 views

How to merge multiple query results into one

1. SELECT COUNT( * ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name 2. SELECT SUM( inscan ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '...
1
vote
1answer
1k views

SELECT count(*) from a materialized view with unique index is slow

I have a materialized view with a unique index (on the id column) and both of these queries are slow (they scan the whole table): SELECT count(DISTINCT a.id) from the_view a; SELECT count(*) from ...
1
vote
1answer
100 views

Repeat total count value

I have a table called paths that looks like this: id parent_id -- --------- 1 null 2 1 3 2 4 3 5 4 6 5 7 5 8 5 9 ...

1 2 3
4
5
8