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
1
vote
2answers
431 views

Count even deleted rows

Is it possible to count even deleted rows in a SQL Server table? Let's say we have a table Contacts. I'll add 5 new contacts, and hit count on it. It should return value of 5. Now, when I delete a ...
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, ...
0
votes
2answers
2k views

SELECT COUNT of multiple column under the same table

Im blowing my brain on this one. I think I am making this harder than I need to. Im trying to run a mySQL query to SELECT a COUNT of members which have any values in any column. my example table is as ...
0
votes
1answer
54 views

How to speed up counting and filtering on table with a lot of columns and filter conditions?

I have TABLE_A in my database that has a lot of columns. Let's say there are 100 columns: COLUMN_1, COLUMN_2, COLUMN_3, .. COLUMN_100 There are so many columns not because of denormalized design, ...
1
vote
1answer
513 views

How to display all the dates in the date-range having group by dates?

Suppose I have a table called “products”: Productive product-name product-quantity created-date 111 ABC 10 1/10/2018 222 XYZ 20 ...
1
vote
1answer
1k views

Bitmap Heapscan is slow

I have 2 tables: mailings and form_responses. The form_responses table has a foreign key with the mailings table, so a mailing has one-to-many form_responses. What I want to do is calculate mailing ...
1
vote
1answer
42 views

COUNT for instances with no value

I have a query that looks for and counts orders that have been written in a medical record that have a specific display group SID. I have 8 medical centers and I want a count for all of them even if ...
0
votes
2answers
59 views

Calcutation at SQL level

I have this query: SELECT * FROM ( SELECT COUNT(lead_status) as 'Pending files' FROM vw_file_registrationlist WHERE lead_status_id = "1" || lead_status_id="2" || lead_status_id="4" || ...
1
vote
1answer
7k views

Select unique value pairs that occur (count) more than once inside a table

How can I retrieve an row with unique value pairs that occur more than once... Heres an example of what I'm what I am trying to.. SELECT column_fk_id1, column_fk_id2, COUNT(distinct column_fk_id1, ...
0
votes
1answer
55 views

Looking for another method to count in a single query

So, I found this article is very useful Join two tables and return data and count in a single query and here Single query to return counts over different IDs in a single record for some IDs or ...
1
vote
2answers
101 views

Count the total occurences for each day in given dates

I have this table and don't enough SQL to make a query to achieve the result shown in the image below. CREATE TABLE myDates ( id integer NOT NULL, startDate date NOT NULL, endDate date ...
1
vote
1answer
207 views

Count where any 3 columns have values (not null)

I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 ...
1
vote
1answer
482 views

Postgres row count

I have a table with a row count of 985 records. But it is really slow to query this table. # select count(*) from movie; count ------- 985 Then I perform query using EXPLAIN ANALYZE The row ...
0
votes
2answers
90 views

Keep count in temporary variable for later use in query

I have used WITH query for this as below WITH my_count AS ( SELECT count(emp_name) FROM employee WHERE joined_date < '2017-11-30'::Date AND region = 'UK' ) INSERT INTO ...
2
votes
1answer
463 views

Calculate quantity of tickets based on how many reserved using sub queries

I'm designing a database that will hold ticketing information. The 3 tables I use to track ticketing are tbl_ticket_type to the types of events an event can have tbl_ticket_count to keep track of ...
1
vote
1answer
14k views

Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement

Within an Oracle procedure, after opening a cursor for a select statement, I fail to find a mean to count the number of rows fetched. OPEN mycursor FOR SELECT * FROM TABLE; -- mycursor%ROWCOUNT is ...
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 ...
2
votes
2answers
806 views

Automatically updating a record using count() of another collection

I have been looking at the docs and searching for videos on my problem, but I find nothing. I have a db called HPDASH where I have 2 collections, scripts and serverList. My problem is that I want to ...
1
vote
1answer
158 views

Count 2 tables same query - PG 9.3

I've got two tables that I need a count on both of them. table1: id table2: cid (table1.id = table2.cid) -- Join can be done by this. I already got two queries that I'm using, and wanna put them in ...
0
votes
1answer
481 views

mongo cluster collection count showing different every time

I had a replica set earlier which I have converted to cluster. Current configuration is: 3 shard 4 router 4 config server When I am trying to get count of one of collection sometime it is showing ...
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,...
1
vote
4answers
113 views

Counting orders w/ new items

Scenario: Find out any customers in each factory that have three or more orders which contain new items they have never purchased before. For example, John has: Order A: Item 1, Item 2 Order B: Item ...
1
vote
1answer
4k views

MySQL: SELECT with SUM() and COUNT() as new columns

What is the correct SELECT statement to produce this desired output? +------------+----------+-------------+-----------------+ | QtyOfParts | FamilyID | NumInFamily | TotalQtyOfParts | +------------+-...
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, ...
1
vote
2answers
912 views

Count per week per column PostgreSQL

Let's say I have a PostgreSQL table with 10 columns and 2 weeks (w1 w2) with 5 countries. Something that would look like this : What I would like to do is a count per week and per column like this :
0
votes
3answers
150 views

Rewriting query with subquery to improve performance [closed]

My query looks like that SELECT topics.name, topics.topic_id, topics.creator, ( SELECT COUNT(*) FROM answers WHERE answers.topic_id = topics.topic_id) AS replies FROM topics ORDER BY topics....
2
votes
2answers
5k views

Complex joins in multiple subqueries

Sorry to keep bugging you guys, but SQL is still new to me. I have these tables: Store_location, Product, Sizes, Sells, Available_in, Offers, and Currency. The goal is to be able to run one query with ...
-1
votes
2answers
149 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
112 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
122 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
553 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
636 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 ...
2
votes
2answers
5k 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
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 ...
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 ...
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
83 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
116 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
3k 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, ...
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 ...
1
vote
1answer
642 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
66 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
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
1
vote
1answer
2k 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
116 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
47 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"."...