Questions tagged [count]
COUNT : an aggregate SQL function that is used to count the number of rows.
452
questions
2
votes
2
answers
187
views
sum() of aggregate count()?
I have a query where I pull the domain portion from email addresses out of a column and then count how many users from which domain I have.
How do I get the total count (all users over all domains) in ...
- 155
0
votes
2
answers
86
views
How to get records which meet conditions stored in another tables
Problem: How to get passed requirements where there are 4 tables in such relation
requirements->links<-test_cases test_cases->results<-result
Scenario:
Table1 - requirements
req_id
...
- 11
1
vote
1
answer
66
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 (
...
- 13
2
votes
1
answer
58
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 ...
- 75
0
votes
2
answers
58
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:
...
- 29
0
votes
1
answer
108
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 (...
- 101
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
342
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 ...
- 111
0
votes
0
answers
46
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 ...
- 29
1
vote
1
answer
59
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,
...
- 29
1
vote
1
answer
222
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
551
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
,...
- 75
2
votes
3
answers
69
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
...
- 75
0
votes
1
answer
408
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
32
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
564
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
453
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
37
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
94
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
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 ...
- 159
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 ...
- 159
-1
votes
1
answer
62
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
194
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
53
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
394
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
1k
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
1
vote
1
answer
4k
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. Here is how the a simplified table looks like:
CREATE TABLE test (
id bigint,
...
- 135
0
votes
1
answer
33
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
100
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
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 ...
- 151
0
votes
1
answer
75
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 ...
- 151
5
votes
2
answers
292
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
898
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
516
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
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 ...
- 21
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
...
- 183
0
votes
2
answers
102
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
177
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(*) ...
- 143
-2
votes
1
answer
23
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
5
votes
3
answers
9k
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
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 ...
- 195
-1
votes
1
answer
12k
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
5k
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
852
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
...
- 183
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 ...
- 153
0
votes
1
answer
50
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
258
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
357
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;
...
- 447
2
votes
1
answer
483
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