Skip to main content

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

PostgreSQL - Sum all row that satisfy a condition into a single row

I'm trying to extract some statistics from a Postgres database and I made this query: SELECT city, job_count, TO_CHAR(job_count * 100 / SUM(job_count) OVER (), '90D00%') AS job_share FROM ( ...
Mateja Maric's user avatar
2 votes
1 answer
53 views

Query for single return meeting multiple criteria and values

I'm trying to figure out a way to count an ID once when multiple criteria are met in one column while displaying a total of the same criteria in another column. The criteria for the first column can ...
tkmagnet's user avatar
0 votes
2 answers
53 views

Mysql count Same Two or More Same Record as One and Summarize It

Good Afternoon all, I have two same data record or more in MySql table. How i can counting the same data as one and zero if have only one data based on flag status pair. Below is an example of data: ...
epm.007's user avatar
  • 29
0 votes
1 answer
75 views

Optimized count for large table using triggers, views, or external cache

I have a public API method that calls a Postgres (14) database and returns a paginated list of rows belonging to a user along with a total count and page index. The count is very costly to perform (...
JackMahoney's user avatar
3 votes
5 answers
2k views

How to write an SQL query where the count of 2 different attributes is the same?

I created two different tables and then decided to put them (=) to each other however, what I am confused about is whether (=) is allowed to be used like that. I tried using IN but I didn't know where ...
sara khalil's user avatar
1 vote
2 answers
267 views

Extremely slow COUNT query on Aurora Postgress Serverless v2 with two tables

I'm designing a web application where sellers can offer their cars, banks provide various financing offers (e.g. 36 months, 25% downpayment, 25% final payment). Buyers come to this web app and they ...
bdadam's user avatar
  • 111
0 votes
0 answers
43 views

How to Count calculated work hours in one month for each labors

Good Day. I have some data like bellow id name in out 1 lala 2023-09-29 07:00:00 2023-09-29 17:00:00 2 lili 2023-09-29 07:30:00 2023-09-29 16:00:00 1 lala ...
epm.007's user avatar
  • 29
1 vote
1 answer
56 views

MySql count using and still show all data even using where clause

Good Afternoon, How i can show all data when using count and where clause in MySql. In my case, I have master data like pict at below. and i using this query to show count the data. SELECT body, ...
epm.007's user avatar
  • 29
1 vote
1 answer
160 views

SELECT / DELETE rows where consecutive null value count is greater than N

I have a table of the following structure, where all columns except the last are non-null: ID receiver send_time recv_time 1 A 00:00:00 00:00:01 2 A 00:00:01 NULL 3 A 00:00:02 NULL 4 A 00:00:03 ...
Edgxxar's user avatar
  • 13
0 votes
2 answers
388 views

Comparing 2 count columns with case

I have 2 tables, size and cost. My query does a count with a greater than comparison of cost and counts different sizes. table1: size u table2: cred t Here is my query: select u.size as size ,...
tkmagnet's user avatar
2 votes
3 answers
62 views

Find percentage based on values for each ID

I have table1 with 2 ID's and 2 values per ID (Y,N). I can count the values by the following query: select id ,count(*) as "total" ,choice from table1 where id in (1,8) group by id, choice ...
tkmagnet's user avatar
0 votes
1 answer
295 views

Deleting rows using count after reaching certain threshold MS Sql server 2012

I'm trying to make a loop query for Sql agent which checks users data and if the criteria is met reward is sent, after that it would delete the records from the table restarting the loop. The issue is ...
Peter's user avatar
  • 79
0 votes
1 answer
29 views

GROUP BY with multiple counts

I'm querying an Invoice table in a MySQL 8.0.28 database that is a child to the Customer table (FK = CustomerId) and I want to be able to get multiple counts based on different criteria over the last ...
snappymcsnap's user avatar
0 votes
1 answer
428 views

MariaDB is in "Sending data" state for too long for COUNT

Recently I have encountered an issue of slow data loads. Upon looking into the Process list I have found out that the issue is on the database side, it gets stuck in the "Sending data" phase ...
jave.web's user avatar
  • 101
1 vote
2 answers
262 views

How to optimize SELECT COUNT with LEFT JOIN?

I have a MariaDB 10.5.10 database, with a table activity made of 150,000 rows. it holds: an id field (integer, primary key) a tenant. There are about 85,000 rows with tenant = 12 an activity_type (...
bfredo123's user avatar
  • 113
1 vote
1 answer
35 views

Counting the Number of Times a Set of Years Appear in a Table?

I am working with Netezza SQL. I have the following table: CREATE TABLE MY_TABLE ( id VARCHAR(50), year VARCHAR(50) ); INSERT INTO MY_TABLE (id, year) VALUES ('123', '2010'); INSERT INTO ...
stats_noob's user avatar
0 votes
0 answers
91 views

Counting Rows From a Value in a SQL text box - please please can anyone help?

I just can't find an example of this anywhere and hoping someone can help. I have a query which returns a list of care services and other fields based on the care service table values and calculated ...
Paula Morrison's user avatar
0 votes
0 answers
27 views

Postgres count values from two diffent columns

My current query looks like this: SELECT idusuario, to_char(intervalo, 'HH24:MI') AS "intervalo" FROM generate_series('2023-06-07 00:00:00', '2023-06-07 23:59:59', '15 minutes'::...
Rafael Hitoshi's user avatar
0 votes
0 answers
31 views

Why is GROUP BY companyName and GROUP BY customerId producing same results? [duplicate]

I'm using the northwind.sql. This is the schema diagram for the northwind.sql. https://brucebauer.info/assets/ITEC3610/Northwind/Northwind-Sample-Database-Diagram.pdf Objective: Write a query to get ...
achhainsan's user avatar
3 votes
1 answer
3k views

Not getting the concept of COUNT with GROUP BY?

This is not my job, neither my homework. I'm just self-learnin from Prof. Donatus course on udemy and these are the problems he used in his course. Objective 1: Your logistics manager wants to know ...
achhainsan's user avatar
-1 votes
1 answer
61 views

question regarding count function

I have a table with states and counties. When I run my count looking for counties, it only returns one column I would like to output the state for the corresponding county: SELECT county, ...
InfinitySteve's user avatar
0 votes
1 answer
162 views

SQL: Count number of distinct records based on attribute value in an xml

Say I have following xml <Operations> <Info Id="2265" cId="2" aId="5" /> <Info Id="2266" cId="2" aId="5" /> <Info ...
Himanshuman's user avatar
0 votes
2 answers
48 views

Count amount players played in same game

I'm trying to count a set of values in a mysql table, where I'm not sure if there is an easy way to do it directly in mysql or if it would be easier to just do it in any programming language after ...
FiFT's user avatar
  • 3
1 vote
1 answer
311 views

How do i found total counts from joining tables? it shows wrong count for some columns

I have tried below query but it shows wrong count select count(DISTINCT schools.id) as total_schools, (select SUM(CASE when schools.is_vts_enabled=true then 1 else 0 END) as vts_enabled from ...
Priyal's user avatar
  • 123
1 vote
1 answer
852 views

Using COUNT returns the same value when counting two different columns

I am new to SQL and am using it for a school project and would like some help. I have a table with multiple columns about a fictional company that provides a bike sharing service. One of the rows I am ...
YellingYeti's user avatar
0 votes
1 answer
3k views

How do I query only rows where multiple instances with the same ID exist with Postgres?

I have a table where I want to gather some statistics, but only for items that are repeated two or more times. To simply, here is how the table looks like: CREATE TABLE test ( id bigint, ...
Alexis Wilke's user avatar
0 votes
1 answer
32 views

How to count all last inputs from all tables in my DB - MySQL

I want to create a query that Generate me a table that count all recent inputs by all users in every table from my DB. If i insert since "2023-01-01". My query should show me all inputs done ...
Rollo's user avatar
  • 29
2 votes
1 answer
82 views

MariaDB counting matching rows in table ~50m rows

Running MariaDB 10.9.4 on Debian 11, I have a table of flights with approximately 50 million rows My structure looks like: id int(11) auto increment flight_number varchar(16) aircraft char(6) ...
James W's user avatar
  • 41
4 votes
1 answer
2k views

MySQL | Set Column default with value of an existing database that is a SELECT result

Problem Having an exsiting database, a we need to add a column to a table, this column being of type int, is there a way to set the default initial value a result of a query in the database? Just to ...
Federico Baù's user avatar
0 votes
1 answer
65 views

MySQL | Related counter on one to many relation

Problem Is there a way to automatically count related tables (one to Many), where the count will automatically increment / decrement depending if we add or remove a related item. So, I could obviously ...
Federico Baù's user avatar
5 votes
2 answers
291 views

Simultaneous aggregate count and full count

I have a table events like this: create table events ( correlation_id char(26) not null, user_id bigint, task_id bigint not null, location_id bigint, type ...
Moshe Katz's user avatar
-1 votes
3 answers
860 views

How to increment value in Master table column with number of records in Details table in performant and accurate way?

I am using SQL Server 2008 and above. I have following two tables: MasterTable MId DetailsCount DEFAULT 0 DetailsTable DId MId In MasterTable.DetailsCount, I store number of records in DetailsTable ...
Amit Joshi's user avatar
-4 votes
1 answer
417 views

Count instanceses of a field in another table [closed]

I have 2 Tables. Table.Workorders with column.LinkID, Table.OptimizationResults with column.LinkIDWorkorder I need to get a Table that has column.LinkID and Count (being how many rows the LinkID ...
Shaun Williams's user avatar
0 votes
2 answers
1k views

How to count rows matching certain conditions within each group?

I have more columns in my table vb10_cartridgedata but the following two columns are my columns of interest: NestID and FailCode. NestID can only contain numbers from 1 to 16. I am trying to count ...
Lalo Quera's user avatar
2 votes
1 answer
2k views

SQL COUNT occurrence of column value on LEFT JOIN

I have seem similar questions on here but none of the answers seem to fit what I am looking for- Say I have one table like so: AssignedTo DeptID Type Mary 5001 Elementary Bob 5002 Middle Bob 5003 ...
EyeSeeSharp's user avatar
0 votes
2 answers
98 views

Does the index in InnoDB and MyISAM keep amount of child nodes in B-trees?

Does the index in InnoDB and MyISAM keep amount of child nodes in B-trees ? If I have an b-tree index on (field1, field2) and we are dealing with MyISAM (to avoid issues with transactions, locking, ...
Dmitriy Gorbenko's user avatar
0 votes
1 answer
150 views

MySQL index of column which needs count by prefix

My table (InnoDB) has a column name varchar(255) NOT NULL, which consists of such data: A:something B:another ... ... I want to make the following query fast, when the data grows: SELECT COUNT(*) ...
xrfang's user avatar
  • 143
-2 votes
1 answer
22 views

Extracting data from two tables

I have( for example) a table called A and a table called B. Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME. I want to find out how ...
Gal Mor's user avatar
5 votes
3 answers
7k views

Optimizing select count result in Postgresql

I'm trying to optimize a table containing 80million+ rows. It takes 20+ minutes to get the row count results. I've tried clustering, vacuum full and reindex but the performance didn't improve. What do ...
starting dba jr's user avatar
1 vote
0 answers
24 views

Best way to create a View which includes counts for multiple one-many relationships?

Consider three entities - Forum, Thread & Topic, with the following relationships Forum has many Threads Forum has many Topics I want to create a View forum_details that displays all columns of ...
Pragy Agarwal's user avatar
-1 votes
1 answer
11k views

SQL Multiple CASE conditions with SUM and count Functions along with JOIN Condition

Kindly help me with the right syntax of mine, really appreciate it, if you could just edit my existing syntax into correct one without alternate options. thanks in advance Table schema CREATE TABLE ...
Harshita's user avatar
2 votes
3 answers
4k views

count(*) queries are too slow even with an index only scan

We currently have a PostgreSQL table that stores audit logs similar to the following: id portal_id created_at action caused_by_id caused_by_type 1 1 2019-09-19 09:39:48.827924 user-logged-in 1 User ...
greekblog's user avatar
  • 129
5 votes
2 answers
831 views

SQL Return Table with where clause and occurrences of value of total table

I am stumped trying to figure out how to achieve this. I know how I would achieve it in C#, but not SQL. Say I have the following table: ID Name RouteId 1 Bob 1001 2 Bob 1002 3 Ana 1001 4 Jim ...
EyeSeeSharp's user avatar
5 votes
1 answer
2k views

Can PostgreSQL use indexes to expedite count(distinct) queries?

Consider the following example: CREATE TABLE test ( id SERIAL, some_integer INT ); INSERT INTO test (some_integer) SELECT FLOOR(RANDOM()*100000) from generate_series(1,100000) s(i); CREATE INDEX ...
Shane's user avatar
  • 153
0 votes
1 answer
48 views

counting minimum sent_time for everyday

Postgres 14 db fiddle updated - Link EDIT: It seems like I need 1 more row: CREATED AT - specifies when conversation was created. SELECT DISTINCT ON (date_trunc('day', sent_time), conv_id, user_id) ...
33a5G7s9t1e3L's user avatar
-6 votes
2 answers
206 views

How can I join 2 tables together, and then count by 2 columns? [closed]

Say I have these 2 tables: What is the best way for me to join these 2 tables, and then count the number of userids per country, per category?
grtlfu_1234's user avatar
0 votes
3 answers
2k views

MySQL COUNT with GROUP BY shows multiple items

Below gives one item but the wrong count SELECT COUNT(activities.id) FROM activities AS COUNT [0] => stdClass Object ( [COUNT] => 189 ) Below gives multiple items but the correct count (...
Jens Törnell's user avatar
0 votes
1 answer
331 views

Count how many variables equal a target value

Is there a smart way to count how many variables equal some target value (in SQL Server)? For example, if I have: declare @a int = 1; declare @b int = 2; declare @c int = 1; declare @target int = 1; ...
HeyJude's user avatar
  • 447
2 votes
1 answer
432 views

Why is count(x.*) slower than count(*)?

explain select count(x.*) from customer x; ... -> Partial Aggregate (cost=27005.45..27005.46 rows=1 width=8) -> Parallel Seq Scan on customer x (cost=0.00..26412.56 rows=237156 width=994) ...
jilen's user avatar
  • 123
6 votes
1 answer
6k views

Performance penalty for COUNT(1) OVER (PARTITION BY NULL)

In my application server, I would like to paginate a dataset using LIMIT and OFFSET, and additionally return the total count of the dataset to the user. Instead of making two remote calls to the ...
Matthew Moisen's user avatar

1
2 3 4 5
10