Questions tagged [count]
COUNT : an aggregate SQL function that is used to count the number of rows.
447
questions
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 ...
- 39
1
vote
2
answers
80
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 ...
- 107
0
votes
0
answers
39
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 ...
- 27
1
vote
1
answer
50
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,
...
- 27
1
vote
1
answer
75
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
...
- 13
0
votes
2
answers
75
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
,...
- 25
2
votes
3
answers
56
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
...
- 25
0
votes
1
answer
83
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 ...
- 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 ...
- 103
0
votes
1
answer
127
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 ...
- 101
1
vote
2
answers
59
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 (...
- 113
1
vote
1
answer
28
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 ...
- 143
0
votes
0
answers
80
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 ...
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'::...
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 ...
- 145
3
votes
1
answer
1k
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 ...
- 145
-1
votes
1
answer
55
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,
...
0
votes
1
answer
93
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 ...
- 197
0
votes
2
answers
44
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 ...
- 3
1
vote
1
answer
215
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 ...
- 123
1
vote
1
answer
409
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 ...
- 11
0
votes
1
answer
2k
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,
...
- 125
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 ...
- 29
2
votes
1
answer
40
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)
...
- 41
4
votes
1
answer
884
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 ...
- 145
0
votes
1
answer
57
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 ...
- 145
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 ...
- 175
-1
votes
3
answers
741
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 ...
- 121
-4
votes
1
answer
284
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 ...
0
votes
2
answers
664
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 ...
- 21
1
vote
1
answer
1k
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
...
- 173
0
votes
2
answers
80
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, ...
0
votes
1
answer
103
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(*) ...
- 145
-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 ...
- 1
4
votes
2
answers
4k
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 ...
1
vote
0
answers
23
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 ...
- 195
-1
votes
1
answer
9k
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 ...
- 3
2
votes
3
answers
3k
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
...
- 129
5
votes
2
answers
766
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
...
- 173
5
votes
1
answer
1k
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 ...
- 153
0
votes
1
answer
46
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)
...
- 119
-6
votes
2
answers
149
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?
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 (...
- 111
0
votes
1
answer
272
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;
...
- 437
2
votes
1
answer
336
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)
...
- 123
6
votes
1
answer
4k
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 ...
- 197
1
vote
1
answer
704
views
Triggers: counting rows in a bridge table after INSERT
I'm using a trigger in order to make a Statistic Table. I have this many-to-many relationship where I have an Order that can have multiple Product's and viceversa. So after an INSERT on Order, using ...
-1
votes
1
answer
2k
views
Update all rows with a one to many relationship count in the same table in MYSQL
I am trying to update all rows of a MYSQL table with the total amount of referred users for each user. 1 user can refer multiple users and I need to run a MYSQL query to update the invite count (total ...
1
vote
1
answer
1k
views
How to count number of occurrences of each key in a MongoDB collection efficiently (one pass?)?
I'm a MongoDB noob and I have a MongoDB collection with millions of documents, each with tens of keys (average ~ 60, large variance) from a set of around 100. I'd like to get a count of each key ...
- 111
-2
votes
1
answer
967
views
Count rows from multiple tables with similar names
I want to have 2 columns: Table Name and Row Count
When I run this, I get 12 table names in my database. How do I then count the rows in each one without having to union 12 count queries together:
...
