Questions tagged [count]

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

Filter by
Sorted by
Tagged with
-1 votes
0 answers
25 views

Perhaps I've made a mistake about "ORDER BY" statement but I can't figure out

I want to show the summary order by the quantity of each group ascendingly then alphabetically but it's not working as expected. 🤔 [1] [1]: https://i.stack.imgur.com/SOCso.png ( SELECT ...
user avatar
  • 1
0 votes
1 answer
39 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; ...
user avatar
  • 331
0 votes
0 answers
12 views

How to count data types in columns for each table in PostgreSQL?

I need to get the amount of datatypes in each table from all databases. Is there a decent way to perform this query in PostgreSQL? I just need to write a query that would return a list of data types ...
user avatar
2 votes
1 answer
66 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) ...
user avatar
  • 123
0 votes
0 answers
11 views

SQL QUERY Counting Subsequent Inner Join

I am learning SQL for my DAD-220 class and hit a wall on formulating a query. I have tried searching and reading and thrown quite a few queries at MySQL but cannot figure the correct query. I have 2 ...
user avatar
3 votes
1 answer
90 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 ...
user avatar
0 votes
1 answer
34 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 ...
user avatar
0 votes
1 answer
48 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 ...
user avatar
1 vote
1 answer
163 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 ...
user avatar
-2 votes
1 answer
132 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: ...
user avatar
0 votes
1 answer
45 views

Query to find the count of all the tables in a MySQL database

We have been using the following query to find the size (i.e. row count) of all the tables in a database: SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = '...
user avatar
  • 235
0 votes
1 answer
25 views

How can I force my count to only count the number of

I'm using SQL in PDQ inventory to generate a report. I'm trying to provide a count of only one set of data, the Display Adapters (Should be between 0 and 4). So some systems have more than one display ...
user avatar
1 vote
1 answer
138 views

SQL query display # of events per day between two time ranges

I have the following table: id start_date end_date status ---------------------------------------------------------------------- 1a 2020-04-09 21:34:00.000 ...
user avatar
  • 13
0 votes
1 answer
53 views

SQL query that is able to count days based on event queries

I have a table with the following data: id date_changed color_start color_end ------------------------------------------------------------------- 1 2020-05-27 16:33:52....
user avatar
  • 13
1 vote
2 answers
88 views

What are the Cons of using triggers in place of count(*) for counting related entities?

What are the cons of using the below approach? What steps can be taken to prevent someone from incorrectly updating the count? Is the below approach an anti-pattern? If yes, what better approaches ...
user avatar
0 votes
2 answers
23 views

Count dated entries between dated milestones from 2 tables

I have a set of milestones with random dates on table 1 and a set of items with random dates on table 2 (see below). Table 1 Milestone MilestoneDate A 01/01/2021 B 03/15/2021 C 05/27/2021 D 09/09/...
user avatar
0 votes
1 answer
13 views

MySQL How can I create a dynamic result of numbers grouped by an "upto" value with a count

I'm looking to group a set of numbers by an "upto" value and show a count of how many results are up to this value omitting any zeros e.g. data could be 38600, 46526, 35813, 26629, 36127, ...
user avatar
2 votes
2 answers
93 views

improve performance of large scan calculation in PostgreSQL table

I have a table flags that collects user contributions for each distinct polygon (flag_value, in this case a 'YES') during their usersession. The idea is to count how many users said YES out of the ...
user avatar
  • 123
-1 votes
1 answer
50 views

I want to count the answers of a multiple choice questions, but it's only counting the single entries

$sql = "select count(*) as total_count, COUNT(if(O13b= '1',1,null)) AS anganwadi, COUNT(if(O13b = '2',1,null)) AS manganwadi, COUNT(if(O13b = '3',1,null)) AS Noanganwadi, ...
user avatar
-2 votes
1 answer
32 views

Having trouble with where subquery count

Assume all of the table and field names are correct. I've tried this a few different ways but this is the only that makes sense needing the join in both. This is the question: SQL query to get the ...
user avatar
1 vote
2 answers
138 views

Oracle simple count(*) SQL query takes "forever"

I'm encountering an "issue" (at least a strange/slow behavior) with a new installation of Oracle 12c on Windows Server 2019, with this query which takes at least 5 minutes to complete: ...
user avatar
  • 111
2 votes
1 answer
709 views

Is it possible to count non-null columns within each row?

Suppose this data: id firstname surname val1 val2 val3 1 joe blogs 1 (null) (null) 2 jane doe (null) 2 5 3 ...
user avatar
2 votes
3 answers
277 views

Count number of occurences per location per hour in PostgreSQL

I have a dataset in Postgres of boat locations on waterways. Here is a sample of the table: boat_id ts waterway_id Boat_A 2019-01-01 16:29:11 WW_01 Boat_A 2019-01-01 17:03:04 WW_02 Boat_B 2019-01-...
user avatar
  • 31
0 votes
1 answer
348 views

Sql: Count Null and Non-Null values for set of tables and fields dynamically

I would like to create dynamic sql query where for the set of tables and fields from information schema I will count null and non-null values of these fields. I have idea to use it something like this ...
user avatar
  • 11
0 votes
2 answers
114 views

Improving accurate count mechanism in PostgreSQL

I have a problem I have been facing for some time and was ignoring for a while. The problem of counting rows. I have read that it is slow as the PostgreSQL has to validate each row thus have to do ...
user avatar
0 votes
2 answers
21 views

Count results of table by date [closed]

I am trying to query the number of transactions that occur on a given day with the following query select COUNT(seq_no) from "table" where start_time >= '2020-04-01 00:00:00' and ...
user avatar
  • 75
3 votes
1 answer
121 views

How to count absence days from differents shift?

I have a problem when in the counting of the employee absences depending on different shifts, so all employees do not have the same shift, and what I should get is the group and the absences of this ...
user avatar
  • 61
-1 votes
1 answer
422 views

Oracle SQL - How to group by a column and count other column for each different value?

I'm struggling trying to get the desired result when querying 2 tables. These are the tables: Table1 column1 (fk) column2 other_columns... 2 a 2 a 3 c 4 a 4 b 4 b 4 d Table2 column1 (pk) ...
user avatar
  • 3
0 votes
2 answers
583 views

mySQL subqueries calculating winning percentage

I have a table where I store the data of a random dice game where you win if you get a sum of 7 between the two dices. Here's the table: id result dice1 dice2 player_id 1 LOSE 4 4 9 2 LOSE 4 5 7 3 ...
user avatar
  • 1
0 votes
2 answers
65 views

Count distinct instances of a SKU and count all items in all instances containing that SKU

This is on MSSQL/Azure SQL. I have the following Sales Table: Line_Item_ID Sales_Order_ID SKU 1 1 4 2 1 1 3 2 4 4 3 1 5 3 7 6 3 4 7 4 4 8 5 1 9 5 8 I am trying to get an output that shows ...
user avatar
1 vote
2 answers
105 views

SQL - count rows with same value

I would like to group and list all the rows that have the same value (gruppo_muscolare.nome) and count how many values (esercizio.nome) are related to the same value, but if I use GROUP BY then I only ...
user avatar
0 votes
0 answers
75 views

MySQL count limit on COUNT + GROUP BY results for performance benefit

I have a very large MySQL8 database, currently using the InnoDB engine, that I have to perform some COUNT() queries on. Specifically, something like this: SELECT parameter, COUNT(*) FROM table WHERE .....
user avatar
  • 1
0 votes
0 answers
38 views

MySQL - Counting multiple parameters from the same subquery

I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the ...
user avatar
  • 1
1 vote
1 answer
331 views

MySQL JOIN COUNTS from another table

I have this two tables: Table members id full_name sex 1 John Smith 1 2 Carol Danvers 2 3 Clark Kent 1 4 Lois Lane 2 5 Diana Prince 2 Table member_emails id member email 1 1 ...
user avatar
0 votes
1 answer
54 views

how to display running count of records in query

I am working on SQL SERVER 2012, and I have been trying to figure out how to include a running count of records in a group by statement. For example, the query would look something like: SELECT ...
user avatar
-1 votes
1 answer
35 views

How to get distinct count on sub table joined via two different way

I have 3 below tables in my db CREATE TABLE main_table ( id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE NOT NULL ); CREATE TABLE middle_table ( id SERIAL PRIMARY KEY, name ...
user avatar
  • 149
1 vote
1 answer
522 views

How to get count 0 using group by if record not exist in the same table?

I need some expert advice please. I have a table with Employees and eCodes in SQL server. I want to get the count of eCodes for each employee but in some cases an employee doesn't have a certain eCode....
user avatar
0 votes
2 answers
56 views

Count Rows that exist before and not after and after but not before

I have an Azure SQL Database, one Table that has 3 Relevant Columns: Brand, ID and Month/Year and a Temporary Table that simply lists the Month/Year with an ID. The table shows a list of all IDs that ...
user avatar
1 vote
2 answers
86 views

EAV - COUNT()/SUM() on column and row values with and without NULLs

Assume that there is a table demo with these data: id userID title data 1 1 DATE 01-01-2021 2 1 COLOR RED 3 2 DATE 4 2 COLOR YELLOW 5 3 DATE 04-01-2021 6 3 COLOR RED 5 4 DATE 04-01-2021 6 4 ...
user avatar
0 votes
1 answer
50 views

Group by Multiple columns and then count different value of same column

I want to achieve the result which tells me the number of males and females of each disability types in each district. each district can have multiple disabilities. So far i have reached the ...
user avatar
1 vote
4 answers
251 views

High performance count with where clause and dynamic data

I am looking for a high-performance approach to solve this problem. I have a table on a SQL-Server 2019 with 316 columns and several million rows. For this query, there are only a few of those columns ...
user avatar
  • 13
0 votes
2 answers
2k views

MySQL - Get counts of `car models` by `car make`

I have a table with cars data, i.e. Makes, Models, & Year. I want to get the count for each Car with how many models it has... something like the image below: This is the SQL query I am using ...
user avatar
  • 111
1 vote
1 answer
55 views

Join tables and summarize rows based on condition

I have two tables. One containing countries with an ID and the geometry The other containing customers with a name, geometry and a boolean value showing if they are active or not. Countries iD ...
user avatar
1 vote
2 answers
52 views

How to order by COUNT(*) of unique entries in GROUP BY?

I search through a tag_map as SELECT ArticleID,COUNT(*) AS C FROM WordMap WHERE WordID IN( SELECT WordID FROM Words WHERE Word IN ('word1', 'word2') ) GROUP BY ArticleID ORDER BY C DESC I want to ...
user avatar
  • 4,179
0 votes
1 answer
15 views

How do I select the total count of records over the past week, including days which have no records in MySQL

Essentially, I need a query which can output something like: +------------+-------+ | date | count | +------------+-------+ | 2020-09-30 | 10 | | 2020-09-29 | 3 | | 2020-09-28 | 0 | | ...
user avatar
2 votes
2 answers
2k views

Count missing values in each column of the table in PostgreSQL

Question: Which column of fortune500 (a table) has the most missing values? To find out, I have checked each column individually mentioned below, which is very tedious. SELECT SUM(CASE WHEN ...
user avatar
1 vote
1 answer
85 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 ...
user avatar
  • 11
0 votes
0 answers
22 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....
user avatar
  • 101
2 votes
1 answer
223 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 ...
user avatar
2 votes
2 answers
39 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....
user avatar
  • 123

1
2 3 4 5
9