Questions tagged [count]

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

Filter by
Sorted by
Tagged with
2
votes
1answer
104 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 ...
2
votes
3answers
145 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-...
0
votes
1answer
81 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 ...
0
votes
2answers
106 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 ...
0
votes
2answers
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 ...
3
votes
1answer
113 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 ...
-1
votes
1answer
62 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) ...
0
votes
2answers
186 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 ...
0
votes
2answers
46 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 ...
1
vote
2answers
46 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 ...
0
votes
0answers
25 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 .....
0
votes
0answers
30 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 ...
1
vote
1answer
55 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 ...
0
votes
1answer
21 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 ...
-1
votes
1answer
30 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 ...
0
votes
1answer
82 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....
0
votes
2answers
44 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 ...
1
vote
2answers
57 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 ...
0
votes
1answer
48 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 ...
1
vote
4answers
157 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 ...
0
votes
2answers
581 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 ...
1
vote
1answer
43 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 ...
1
vote
2answers
49 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 ...
0
votes
1answer
12 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 | | ...
2
votes
2answers
937 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 ...
1
vote
1answer
35 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 ...
0
votes
0answers
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....
2
votes
1answer
34 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 ...
2
votes
2answers
37 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....
1
vote
1answer
27 views

Mysql How to update n rows using count function?

In the following table, I want to set delete = true if the total records for same orgid exceed 500 and I want to do it according to createdate such that if records exceed 500 the old records get ...
0
votes
2answers
49 views

Performing SQL counts with and without a WHERE clause from the same table

This is my first post so I apologize if I am not concise enough. I am trying to come up with an SQL query to identify data quality issues. Here's the sample table: DeviceOS Bytes Roku 10,...
0
votes
1answer
31 views

Case overcounting what is expected

So I am attempting to setup a query that can track actions that are automated within our web system. The first thing I am attempting to check is the reminders we are sending out to customers. I have ...
0
votes
1answer
22 views

total drinks per day column [closed]

Attempting to select a table with a column where the customers total drinks ordered per day are calculated by the odate and drink number. EG. Claude orders 10 drinks total on the 2020-1-12 drinker | ...
1
vote
1answer
33 views

Count nested joined table records only if the table associated via bridge table have two records with given names

I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert ...
0
votes
2answers
23 views

How to check if staff member has end date with a Null value

I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish. ...
0
votes
1answer
44 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
1
vote
1answer
131 views

SQL (Redshift) SELECT COUNT from CSV column

Hi exchange community, Problem statement and details: I am trying to query a column that contains CSV data to produce a count of instances of each value in the table. There are a total of 14 ...
0
votes
1answer
89 views

SQL count(*) grouped by date range

I have a table as follows: DROP TABLE IF EXISTS #PersonnelRecord; CREATE TABLE #PersonnelRecord ( person_id int NOT NULL, company_id int NOT NULL, entry_date date NOT NULL, exit_date ...
0
votes
1answer
78 views

List top N rentals for each month

I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server. The database is the Sakila Sample database and the table is rental. Here are the first several rows in ...
-1
votes
1answer
25 views

SQL joining three tables and displaying column with values zero count

Im trying to display all the city names and their counts of events where the event price = 0 create table event (eventID decimal(12) not null primary key, AddressID decimal(12) not null, eventName ...
0
votes
2answers
330 views

Trying to Count Multiple Columns SQL

I'm trying to count multiple columns in a select statement for a class I'm in. Everything is coming from the same table. I want to know how many times that person paid something and how many times ...
0
votes
1answer
31 views

How can I rearrange these two queries to get the second column from inside the IN()

How can I get the random.id column from inside this first select select id, name from random where id IN(1,2,3,4); +----+-----------+ | id | name | +----+-----------+ | 1 | Frank | | 2 | ...
0
votes
1answer
19 views

MySQL : count occurences on different columns in a same query

I've got his table: +----+------+---------+ | id | user | upgrade | +----+------+---------+ | 0 | A | NULL | +----+------+---------+ | 1 | B | NULL | +----+------+---------+ | 2 | ...
0
votes
1answer
75 views

Selecting difference between two tables returns different number of results than counts of same two tables

I'm using SQL Server and when I run SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2 it returns 1610 entries. When I query SELECT COUNT(*) FROM TABLE1 SELECT COUNT(*) FROM TABLE2 I get 6399 and ...
0
votes
1answer
66 views

postgresql cumulative counts in date range

I'm trying to get the cumulative count of rows (by group_id) between two dates that represent a time period where the row was active. I have a table like this: group_id | id | type | start_date ...
0
votes
1answer
14 views

SELECT COUNT in same table based on current row value within a single query

Let's say I have a table like below uid | value | date_added ----------------------------------- 14152 | NA4156 | 2019-03-01 14153 | EA4656 | 2019-03-02 14152 | EA4656 |...
-2
votes
1answer
129 views

SUM the occurrences (COUNT) of 2 table [closed]

i have 2 databases with same columns [id, name] and many many rows. I want to make query separatly and then make a union. This because the extremely dimension of the 2 databases. SELECT id, count(...
0
votes
1answer
853 views

Calculate products count based on category tree table

It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent. How can i do that SQL/Postgresql? ...
1
vote
2answers
47 views

WHERE clause not preventing return of row but changing values for aggregate function (here: count)

I have recently discovered a PostgreSQL behavior which is strange and problematic in my opinion. Consider this simple query SELECT 'something confidential' WHERE FALSE; The nonsense clause ...
0
votes
1answer
33 views

MySQL COUNT based on a condition [duplicate]

I have a MariaDB database, in which I have this table: ID | Item Name | Item replaced | ----------------------------------------- 0 | Item 1 | Replaced 1 | Item 2 | 2 | Item 3 | ...

1
2 3 4 5
8