Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [count]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
14 views

Use plpgsql to count records selected for output AND output those records

My query SELECTs 1 row from table1, which has a number of fields plus one very long text field. It JOINs with a second table2, that may have many matching corresponding rows. I would like to be able ...
1
vote
2answers
25 views

How can I have an aggregate function in the WHERE clause?

Using PostgreSQL 9.3.24 Issue Just simple operation, but I don't know how to do that best simplest way. How can I display results only when sum of available_counts for same product_id is 0 and ...
0
votes
1answer
12 views

How to optimize count(*) using only foreign key index on PostgreSQL

I have a web app that extensively uses data tables that connect to the database to fetch data. The problem though, it always needs first to count the total results before it could paginate the data by ...
1
vote
1answer
28 views

Filtering on two separate colums

The schema I'm using MySQL Consider a table with columns id uuid type char is_active Boolean Here are some facts about the data in this table type is either 'A' or 'B' There will be a maximum of 2 ...
0
votes
1answer
40 views

Count up numbers from a specific column [closed]

Hi there was wondering if someone could help me out, and can give me a little code example. i have a table named "projecten", and columns named "pstatus and pafgifte" these columns have numbers only. ...
2
votes
3answers
118 views

Query to get counts of values per column

I have a big table of vendor-supplied data (that I can't change around much) with about 315 columns. I suspect that many of the columns are not being used (or at least not consistently). I'd like a ...
1
vote
1answer
17 views

Postgres: Getting the “partition number” instead of row_number

Figure a table with the following data, how to exclude 'alone' values? id; data (1, 'foo'), (2, 'foo'), (3, 'foo'), (4, 'bar'), (5, 'bar'); (6, 'jak'); I try with this: select id, data, row_number()...
0
votes
1answer
31 views

Sub Query with group by returns more than 1 row Issue

I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result. This is my query SELECT Count(*) ...
0
votes
0answers
15 views

select rows and calculate number of rows based on parent column value

I have a total of three tables with a many-to-many relationship. 1.) users id | email ----------- 1 | usera@example.com 2 | userb@example.com 3 | userc@example.com 2.) accounts id | userId | ...
2
votes
1answer
39 views

More efficient group by to count items in group

My query is: SELECT COUNT("EventType"."id") AS "eventCount", "EventType"."id" AS "EventType.id" FROM "events" AS "Event" INNER JOIN "event_types" AS "EventType" ON "Event"."eventTypeId" = "...
0
votes
1answer
17 views

Group by unique items in an array column and count records that contain each item

I have a table that looks like this: records ------------------------------------- id items[] ------------------------------------- 1 {toaster,piano} 2 {car} 3 {car, toaster} 4 {tv}...
0
votes
1answer
51 views

Optimize slow COUNT in MySQL subquery

I have the following query that takes minutes to complete: SELECT DISTINCT Designation, Model, ( SELECT COUNT( Equipment.EquipmentID ) FROM Equipment INNER JOIN EquipmentDesignation_Vw ...
0
votes
4answers
70 views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
-1
votes
1answer
37 views

Month on Month count to determine variance of table growth

Good Afternoon Team, Hoping you can help me . Every month i get a count of records associated to a table. What i would like to do is month on month calculate the % variance (between jan vs feb, feb ...
2
votes
2answers
34 views

Summing the count of two columns in postgres that contain the same value

I want to sum the total number of occurrences of one value across two columns in the same table. So, an example would be: id |node1 |node2 1 | 111 | 123 2 | 122 | 124 3 | 111 | 125 4 ...
0
votes
2answers
23 views

Count two different columns from two different tables and throw into a single variable

I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. ...
0
votes
1answer
17 views

How to get the customer payment count for each row in MYSQL

I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ? SELECT c....
0
votes
0answers
29 views

MySQL Count rows of multiple tables

CREATE OR REPLACE VIEW organizedevent AS SELECT o.organizerID AS "Organizer ID", o.organizerName AS "Name", o.contact AS "Contact", COUNT(e.eventID) AS "...
0
votes
0answers
22 views

How to implement row count for REST API?

I’m building a REST API backed by PostgreSQL. I'm encountering something I don’t think is very niche, but I’m having trouble finding any discussion of it. Good REST API practice dictates that if a ...
0
votes
1answer
21 views

MySQL Count(Table1.ColId) Where IN (any of Table2.Col1 ,Table2.Col2)

I have 2 Tables Table 1 has Id, Name Table 2 has Id, Col1_IdVal, Col2_IdVal,Col3_IdVal,Col4_IdVal I would like simple solution for Count(T1.Id) WHERE T1.Id In (T2.Col1_IdVal,T2.Col2_IdVal,T2....
0
votes
2answers
13 views

Select value based on a test

I have two tables user_session_logs and blacklisted_ips. The first one for logging access logs and the second one is for blacklisted ips, and both tables have a column named ip_address. I need to ...
0
votes
1answer
41 views

How to query results from a huge postgres database?

I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly! Even to find unique rows of a column it takes more than 10 minutes. Either I use a ...
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 | ...
0
votes
1answer
16 views

count and group_concat

I have data in a table like so: +---------+----------------------------------------------------+ | videoid | ips | +---------+---------------------------...
1
vote
1answer
21 views

Get the combined row count by month from 3 tables

Using Postgres 10+, I have three tables with similar structures, where I'm trying to get a count of rows grouped by months. I have a functional query working for two tables, as below: CREATE TABLE ...
0
votes
1answer
18 views

Join on unnested integer array column and count occurrences in array

I am trying to join two tables after I have unnested one of them. The tables are: CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60)); CREATE TABLE training_modules (id ...
0
votes
0answers
67 views

How to get hit (number of possible results of a query) from a table with more than 100 million records in Postgres

We have a table X in postgres v11, where it has 5 columns like A B C D E. We all columns are indexed and there are all integer values. We have around, 100 milion records, but soon will turn into 800 ...
2
votes
2answers
168 views

SQL script to count rows for all views in a database with SELECT-only access

I need to know the row count for every view in a third-party database that I have been given SELECT-only access to. The following script works for tables but not views: SELECT SCHEMA_NAME(A....
2
votes
1answer
42 views

join table and count them SQL based on segment

I have 2 tables and I'm trying to join and count how many people have answer each questions. I'm trying to find a better way to have only two column. the first one will be of people who answer "Just ...
0
votes
0answers
23 views

Time wise user online count

I'm not a DB expert but i'm trying to get time wise user online count to show in a graph. I have manage to get online user count for current time but how to get for a time span ? i need each minute ...
2
votes
1answer
90 views

Improving performance of count where only result > 0 matters

I have following query: select count(*) -- rp.tag from submission inner join post rp on submission.id = rp.submission_id where user_name = ?1 and lower(rp.tag) like ('%verified%') and not lower(rp....
3
votes
2answers
51 views

Query is faster with additional JOIN

SELECT COUNT(DISTINCT User_id) FROM Session GROUP BY Floor_id; SELECT COUNT(DISTINCT User_id) FROM Session JOIN Floor ON Floor.id = Floor_id GROUP BY Floor_id; The second query runs 20x faster ...
0
votes
1answer
53 views

Calculating count on same table based on condition

So I need to calculate the amount of Clients who's LastName start with the same letter, then use that count as part of an Reference number. I.e. something like this: declare @...
0
votes
1answer
12 views

Help with optimizing Oracle Query - nested with count

I have 2 tables (Students and Cellphone). Columns in Students: StudentID, FNAME, PHONEID Columns in Cellphone: PhoneID, Phonename I want to create this: • Run a query to show the most popular ...
0
votes
3answers
71 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, ...
1
vote
2answers
50 views

Count results after filtering out

I am using MySQL 5.7 and need to count results after filtering out the data. My table structure: +----+---------+---------+---------+ | id | metakey | metaval | tour_id | +----+---------+---------+--...
1
vote
1answer
70 views

How to count records from different tables [closed]

So I'm trying to filter some records out of my select inside a form on my website. Problem is that I currently have this setup: Database Table1 time Number Table2 Number Something I'...
0
votes
2answers
38 views

For states (col) receiving exactly X number of items (row), sort the items by weight (col)

I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package. I am able to get the States with the exact number of packages, but don't know how ...
0
votes
1answer
222 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 ...
1
vote
1answer
31 views

Count Assets Barcodes and Group by Rooms

I have 2 tables Asset Register and LocationRoom. I want to count the number of asset barcodes in each room. I want the output to be the list of Asset Barcodes, Description,RoomNo and count for ...
0
votes
1answer
32 views

Distinct counts from large table are too slow

I have a table with 1.2 billion rows. The table has 60-70 columns, the vast majority of which are empty. The table is indexed on subscription_id, among other indexes. My query is: SELECT count(...
1
vote
2answers
285 views

If count >= row then insert row (substitute for variables and else/if)

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense. So I've got a table that looks something like this: post_id | meta_key | ...
0
votes
1answer
31 views

Issue with Maximo KPI Returning “0” Instead of a Calculated % [closed]

I am attempting create a KPI in Maximo that will count the work orders completed on time and divide it by the number of work orders due for the current month, then multiply it by 100 to return a ...
0
votes
1answer
24 views

Count Total Transaction each specific row even if 0

I have a 3 Columns namely: Payment Channel Payment Name Total Transaction The Total Transaction will be the COUNT of each Payment Channel and Payment Name. In the Payment Channel, values have PC01-...
0
votes
2answers
30 views

How to SELECT most common (viral) from a table and LIMIT results for pagination?

Sorry if it's a dubplicate but I can't find an answer to the question. Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is ...
0
votes
1answer
20 views

Get count of value row

I'm trying to figure out what MySQL code would do the following. Count * from 'users' table (eg; 0,7 rows) then within the count get the count of 'money' based on the value of the user. I'm ...
2
votes
2answers
416 views

how to count distinct value per day with start time and end time

my title may seem a bit confusing but here's the context. I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user ...
0
votes
1answer
49 views

Join 3 tables with where and sorting clauses

I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too. Each camera has a reference of vendor_model as model_id. 10 ...
2
votes
1answer
123 views

Get total number of rows when offset is greater than max

I have a table users that has, say, 42 records. To get the total rows and some other details, I use a window function: select count(*) over() as count, name, id from users And since the consumer app ...
1
vote
2answers
128 views

Count items on condition

In an application where a user can have copies of a mobile application installed on multiple devices I'd like to know how many iphone and how many android apps each user has. I tried a query such as ...