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
1answer
22 views

Counting working days in a month

I wrote this query that suppose to count the working days in a month but I get the following error: [42883] ERROR: operator does not exist: double precision + interval Hinweis: No operator matches ...
0
votes
0answers
21 views

COUNT() leaves me with one row only

I use the following INNER JOIN to get my values from SQLite tables books and book_authors: SELECT * FROM (SELECT book_authors.book_id, book_authors.author_id, book_authors....
2
votes
1answer
380 views

Slow ranking values in MySQL

I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers. I'm supposed to display well over 70 different rankings ...
3
votes
1answer
2k views

Trigger for updating a count of rows from a table into another table

I have these two tables: mysql> select * from orders; +-------------+---------+ | Customer_ID | Item | +-------------+---------+ | A01 | CPU | | A01 | Monitor | | A02 ...
2
votes
2answers
859 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 ...
0
votes
1answer
520 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 ...
2
votes
1answer
27 views

Count the number of occurrences of certain values of TagName column

I want to count the number of occurrences of different sensor rows in SQL, but I seem to be doing it wrong and seemingly I am not visualizing it correctly. If I was doing this in pseudocode in a C ...
2
votes
2answers
31 views

Count rows for each latest status of tickets

ticket_number | datetime | status ---------------+----------------------------+--------- 1 | 2020-08-03 03:52:58.048196 | replied 1 | 2020-08-02 19:55:49....
0
votes
2answers
26 views

Performing SQL counts with and without a WHERE clause from the same table

This is my first post so I apologize if I am not concise enough. I am trying to come up with an SQL query to identify data quality issues. Here's the sample table: DeviceOS Bytes Roku 10,...
0
votes
1answer
411 views

MySQL - Split results of count function into columns derived from a separate column

The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just ...
4
votes
4answers
27k views

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 |'...
4
votes
2answers
5k views

Speed up COUNT(*) when using an index

I have a table similar to this (simplified): CREATE TABLE books ( id INT AUTO_INCREMENT, category INT NOT NULL, PRIMARY KEY (id), KEY (category) ); This table has over 10,000,000 ...
2
votes
1answer
25 views

Mysql How to update n rows using count function?

In the following table, I want to set delete = true if the total records for same orgid exceed 500 and I want to do it according to createdate such that if records exceed 500 the old records get ...
17
votes
2answers
18k 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 ...
0
votes
1answer
95 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
21
votes
3answers
36k views

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 ...
52
votes
1answer
57k 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 ...
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 ...
0
votes
1answer
20 views

Case overcounting what is expected

So I am attempting to setup a query that can track actions that are automated within our web system. The first thing I am attempting to check is the reminders we are sending out to customers. I have ...
0
votes
2answers
236 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 ...
0
votes
1answer
21 views

total drinks per day column [closed]

Attempting to select a table with a column where the customers total drinks ordered per day are calculated by the odate and drink number. EG. Claude orders 10 drinks total on the 2020-1-12 drinker | ...
1
vote
1answer
26 views

Count nested joined table records only if the table associated via bridge table have two records with given names

I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert ...
0
votes
2answers
20 views

How to check if staff member has end date with a Null value

I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish. ...
0
votes
1answer
23 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
1
vote
1answer
33 views

SQL (Redshift) SELECT COUNT from CSV column

Hi exchange community, Problem statement and details: I am trying to query a column that contains CSV data to produce a count of instances of each value in the table. There are a total of 14 ...
0
votes
2answers
4k views

need help joining two tables with a having count clause

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to ...
1
vote
2answers
150 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 ...
0
votes
1answer
53 views

SQL count(*) grouped by date range

I have a table as follows: DROP TABLE IF EXISTS #PersonnelRecord; CREATE TABLE #PersonnelRecord ( person_id int NOT NULL, company_id int NOT NULL, entry_date date NOT NULL, exit_date ...
1
vote
3answers
250 views

SQL Join and Count

I have 3 tables in SQLServer that I want to join so I can count the number of times each SUBJ_NAME occurs for the ITEM_IDs in the LOAN table. CREATE TABLE SUBJT ( SUBJ_ID CHAR(4) PRIMARY KEY, ...
0
votes
1answer
49 views

List top N rentals for each month

I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server. The database is the Sakila Sample database and the table is rental. Here are the first several rows in ...
-1
votes
1answer
25 views

SQL joining three tables and displaying column with values zero count

Im trying to display all the city names and their counts of events where the event price = 0 create table event (eventID decimal(12) not null primary key, AddressID decimal(12) not null, eventName ...
0
votes
1answer
28 views

How can I rearrange these two queries to get the second column from inside the IN()

How can I get the random.id column from inside this first select select id, name from random where id IN(1,2,3,4); +----+-----------+ | id | name | +----+-----------+ | 1 | Frank | | 2 | ...
0
votes
0answers
11 views

MYSQL - Retrieve running total over time of records in foreign table for each record in table

I have a table group & a table members. A group has many members. A single member record can belong to a single group. The members table has a foreign key/column group_id. I need to retrieve, ...
0
votes
1answer
24 views

Trying to Count Multiple Columns SQL

I'm trying to count multiple columns in a select statement for a class I'm in. Everything is coming from the same table. I want to know how many times that person paid something and how many times ...
0
votes
1answer
16 views

MySQL : count occurences on different columns in a same query

I've got his table: +----+------+---------+ | id | user | upgrade | +----+------+---------+ | 0 | A | NULL | +----+------+---------+ | 1 | B | NULL | +----+------+---------+ | 2 | ...
-1
votes
2answers
129 views

SQL Performance: COUNT or extra attribute?

If I want to count the number of a multivalued attribute, which way is faster: a) using COUNT(attribute) on a whole table or b) adding an extra attribute which counts these stored values (to be ...
0
votes
1answer
24 views

Selecting difference between two tables returns different number of results than counts of same two tables

I'm using SQL Server and when I run SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2 it returns 1610 entries. When I query SELECT COUNT(*) FROM TABLE1 SELECT COUNT(*) FROM TABLE2 I get 6399 and ...
0
votes
1answer
29 views

postgresql cumulative counts in date range

I'm trying to get the cumulative count of rows (by group_id) between two dates that represent a time period where the row was active. I have a table like this: group_id | id | type | start_date ...
0
votes
0answers
21 views

How can I have multiple group by in MongoDB aggregation?

My sample data is: [{ "company": "apple", "last_view": "2020-01-01" }, { "company": "google", "last_view": "2020-01-01" }, { "company": "google", "last_view": "2020-01-01" }] Now to ...
4
votes
1answer
4k views

How to get row count from all tables of a schema ( without using information schema )

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
14
votes
2answers
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 ...
0
votes
1answer
13 views

SELECT COUNT in same table based on current row value within a single query

Let's say I have a table like below uid | value | date_added ----------------------------------- 14152 | NA4156 | 2019-03-01 14153 | EA4656 | 2019-03-02 14152 | EA4656 |...
1
vote
1answer
2k 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 ...
-2
votes
1answer
24 views

SUM the occurrences (COUNT) of 2 table [closed]

i have 2 databases with same columns [id, name] and many many rows. I want to make query separatly and then make a union. This because the extremely dimension of the 2 databases. SELECT id, count(...
0
votes
1answer
48 views

Calculate products count based on category tree table

It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent. How can i do that SQL/Postgresql? ...
1
vote
2answers
40 views

WHERE clause not preventing return of row but changing values for aggregate function (here: count)

I have recently discovered a PostgreSQL behavior which is strange and problematic in my opinion. Consider this simple query SELECT 'something confidential' WHERE FALSE; The nonsense clause ...
0
votes
1answer
25 views

MySQL COUNT based on a condition [duplicate]

I have a MariaDB database, in which I have this table: ID | Item Name | Item replaced | ----------------------------------------- 0 | Item 1 | Replaced 1 | Item 2 | 2 | Item 3 | ...
0
votes
1answer
30 views

SELECT with a GROUP BY column?

The situation is like this: I have a table that has one row for each copy of a letter, which also contains the letter ID. I want a single query that outputs each letter copy row with the letter copy ...
0
votes
1answer
24 views

MySQL query help

I need a query that returns the number of liked messages and orders them by the number of likes (from most to least). I have tried: SELECT users.user_id, messages.author, messages....
0
votes
1answer
25 views

Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column [closed]

I want to create a query that will search the table and find the following: If a POL_N entry has ENDO_N entries then the COUNT ENDO_N field will start counting the ENDO_N entries and will get the ...

1
2 3 4 5
8