We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [count]

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

Filter by
Sorted by
Tagged with
-4
votes
1answer
31 views

can I be helped? my querey should = something like .000534 however I get 0

My query result should be 0.000534, but I'm getting 0. Please help me. psql -Upostgres -dcans -c \ 'select (select COUNT(*) FROM brd.gaf_info where ...
2
votes
3answers
12k views

How to use COUNT with multiple columns?

How to use multiple columns with a single COUNT? Assume that there is a table demo with these data: id | col1 | col2 | -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* 1 |'alice' | 'book1'| 2 |'...
-1
votes
1answer
21 views

how to use count with join [on hold]

I'm a beginner to SQL, I have 2 table as follows in which I'm trying to answer question like "how many toys for the each kid?" Kid: (kidid, name). Has 2 rows Toy: (toyid, name, kidsid). Has 2 rows ...
1
vote
2answers
121 views

MySQL Count where all conditions evaluate to 1

What is the best way to get count for all the records, where case evaluates to one for all the conditions. I have the query as: SELECT uuid , COUNT( CASE WHEN (( dt = 1 or level0 = '...
-1
votes
2answers
30 views

db2 select statement from a table name where the table name is a variable [on hold]

I need to select all the table row count from a db2 database. I have a query to select all the schema and table name: select rtrim(tabschema)||'.'||rtrim(tabname) as tableName from syscat.tables ...
-4
votes
0answers
35 views

Count terrible slow

I have the following issue. This query on my database runs in 2 sec select events.*, events.start AS eventFrom, events.title AS ...
-3
votes
0answers
15 views

Count terrible slow from subselect [duplicate]

I have the following issue. This query on my database runs in 2 sec select events.*, events.start AS eventFrom, events.title ...
0
votes
1answer
200 views

Lock the whole table and use transaction [closed]

To perform some large operations, I'd like to lock (write) the tables beforehand, and still be able to use transactions. On this page (comment at the bottom) it's recommended to use SELECT ... FOR ...
0
votes
1answer
162 views

Limit the number of results but show how much would be returned

I have a big problem in T-SQL with large amount of data where I need to limit number of returned rows to 200K but also to show the number of rows that would be returned. It is needed to be done under ...
1
vote
2answers
25 views

How can I have an aggregate function in the WHERE clause?

Using PostgreSQL 9.3.24 Issue Just simple operation, but I don't know how to do that best simplest way. How can I display results only when sum of available_counts for same product_id is 0 and ...
0
votes
1answer
18 views

Use plpgsql to count records selected for output AND output those records

My query SELECTs 1 row from table1, which has a number of fields plus one very long text field. It JOINs with a second table2, that may have many matching corresponding rows. I would like to be able ...
0
votes
2answers
207 views

How to improve query count execution with mySql replicate?

For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table). And its really time consuming when it ...
8
votes
3answers
23k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
0
votes
1answer
12 views

How to optimize count(*) using only foreign key index on PostgreSQL

I have a web app that extensively uses data tables that connect to the database to fetch data. The problem though, it always needs first to count the total results before it could paginate the data by ...
1
vote
1answer
28 views

Filtering on two separate colums

The schema I'm using MySQL Consider a table with columns id uuid type char is_active Boolean Here are some facts about the data in this table type is either 'A' or 'B' There will be a maximum of 2 ...
0
votes
1answer
40 views

Count up numbers from a specific column [closed]

Hi there was wondering if someone could help me out, and can give me a little code example. i have a table named "projecten", and columns named "pstatus and pafgifte" these columns have numbers only. ...
2
votes
3answers
125 views

Query to get counts of values per column

I have a big table of vendor-supplied data (that I can't change around much) with about 315 columns. I suspect that many of the columns are not being used (or at least not consistently). I'd like a ...
1
vote
1answer
19 views

Postgres: Getting the “partition number” instead of row_number

Figure a table with the following data, how to exclude 'alone' values? id; data (1, 'foo'), (2, 'foo'), (3, 'foo'), (4, 'bar'), (5, 'bar'); (6, 'jak'); I try with this: select id, data, row_number()...
0
votes
1answer
40 views

Sub Query with group by returns more than 1 row Issue

I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result. This is my query SELECT Count(*) ...
2
votes
1answer
39 views

More efficient group by to count items in group

My query is: SELECT COUNT("EventType"."id") AS "eventCount", "EventType"."id" AS "EventType.id" FROM "events" AS "Event" INNER JOIN "event_types" AS "EventType" ON "Event"."eventTypeId" = "...
0
votes
0answers
17 views

select rows and calculate number of rows based on parent column value

I have a total of three tables with a many-to-many relationship. 1.) users id | email ----------- 1 | usera@example.com 2 | userb@example.com 3 | userc@example.com 2.) accounts id | userId | ...
17
votes
1answer
66k views

MySQL Count rows from another table for each record in table

SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID I am trying to count the last ...
0
votes
1answer
39 views

Distinct counts from large table are too slow

I have a table with 1.2 billion rows. The table has 60-70 columns, the vast majority of which are empty. The table is indexed on subscription_id, among other indexes. My query is: SELECT count(...
0
votes
1answer
19 views

Group by unique items in an array column and count records that contain each item

I have a table that looks like this: records ------------------------------------- id items[] ------------------------------------- 1 {toaster,piano} 2 {car} 3 {car, toaster} 4 {tv}...
2
votes
2answers
804 views

Automatically updating a record using count() of another collection

I have been looking at the docs and searching for videos on my problem, but I find nothing. I have a db called HPDASH where I have 2 collections, scripts and serverList. My problem is that I want to ...
0
votes
4answers
74 views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0
votes
1answer
76 views

Optimize slow COUNT in MySQL subquery

I have the following query that takes minutes to complete: SELECT DISTINCT Designation, Model, ( SELECT COUNT( Equipment.EquipmentID ) FROM Equipment INNER JOIN EquipmentDesignation_Vw ...
0
votes
1answer
154 views

How to get Average value from one row if have zero or null values in mysql

I'm new in mysql. I want to ask something about get average value from one row in mysql when have a null or zeros value on them. the image below assuming my table And I want to get the average from ...
-1
votes
1answer
37 views

Month on Month count to determine variance of table growth

Good Afternoon Team, Hoping you can help me . Every month i get a count of records associated to a table. What i would like to do is month on month calculate the % variance (between jan vs feb, feb ...
2
votes
1answer
339 views

Slow ranking values in MySQL

I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers. I'm supposed to display well over 70 different rankings ...
2
votes
2answers
42 views

Summing the count of two columns in postgres that contain the same value

I want to sum the total number of occurrences of one value across two columns in the same table. So, an example would be: id |node1 |node2 1 | 111 | 123 2 | 122 | 124 3 | 111 | 125 4 ...
0
votes
2answers
24 views

Count two different columns from two different tables and throw into a single variable

I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. ...
0
votes
1answer
481 views

mongo cluster collection count showing different every time

I had a replica set earlier which I have converted to cluster. Current configuration is: 3 shard 4 router 4 config server When I am trying to get count of one of collection sometime it is showing ...
0
votes
1answer
18 views

How to get the customer payment count for each row in MYSQL

I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ? SELECT c....
2
votes
2answers
1k views

Count where previous record exists in either of two tables

I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another. The table that I'm sampling has a composite PRIMARY ...
0
votes
0answers
30 views

MySQL Count rows of multiple tables

CREATE OR REPLACE VIEW organizedevent AS SELECT o.organizerID AS "Organizer ID", o.organizerName AS "Name", o.contact AS "Contact", COUNT(e.eventID) AS "...
4
votes
3answers
4k views

Join Multiple Tables for Aggregates

How do I join multiple tables for aggregates with Sum and Count functions? The query I'm trying is as below: Select campaigns.id, campaigns.name, Count(landers.campaign_id) As landers_count, ...
1
vote
1answer
122 views

Efficient count query for consecutive time windows

I have a SQL Server Database: in one table, one of the columns represents the time of an event (of type datetime2(7)). Another column represents a product ID. My goal is to specify a flexible time ...
0
votes
1answer
243 views

MySQL - Split results of count function into columns derived from a separate column

The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just ...
0
votes
2answers
117 views

SQL Performance: COUNT or extra attribute?

If I want to count the number of a multivalued attribute, which way is faster: a) using COUNT(attribute) on a whole table or b) adding an extra attribute which counts these stored values (to be ...
0
votes
0answers
22 views

How to implement row count for REST API?

I’m building a REST API backed by PostgreSQL. I'm encountering something I don’t think is very niche, but I’m having trouble finding any discussion of it. Good REST API practice dictates that if a ...
0
votes
3answers
150 views

Rewriting query with subquery to improve performance [closed]

My query looks like that SELECT topics.name, topics.topic_id, topics.creator, ( SELECT COUNT(*) FROM answers WHERE answers.topic_id = topics.topic_id) AS replies FROM topics ORDER BY topics....
0
votes
1answer
21 views

MySQL Count(Table1.ColId) Where IN (any of Table2.Col1 ,Table2.Col2)

I have 2 Tables Table 1 has Id, Name Table 2 has Id, Col1_IdVal, Col2_IdVal,Col3_IdVal,Col4_IdVal I would like simple solution for Count(T1.Id) WHERE T1.Id In (T2.Col1_IdVal,T2.Col2_IdVal,T2....
0
votes
2answers
14 views

Select value based on a test

I have two tables user_session_logs and blacklisted_ips. The first one for logging access logs and the second one is for blacklisted ips, and both tables have a column named ip_address. I need to ...
-1
votes
2answers
149 views

Refining Productivity & Efficiency Query

I'm trying to write a query that is, in effect, a Productivity and Efficiency Report for measuring the statistics of those of us (such as myself) who type up the paper work orders that our mechanics ...
0
votes
1answer
43 views

How to query results from a huge postgres database?

I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly! Even to find unique rows of a column it takes more than 10 minutes. Either I use a ...
10
votes
5answers
2k views

SQL counting distinct over partition

I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A. MyTable Col_A | Col_B A | 1 A | 1 A | 2 A | 2 A | 2 A | 3 b | ...
0
votes
0answers
67 views

How to get hit (number of possible results of a query) from a table with more than 100 million records in Postgres

We have a table X in postgres v11, where it has 5 columns like A B C D E. We all columns are indexed and there are all integer values. We have around, 100 milion records, but soon will turn into 800 ...
0
votes
1answer
16 views

count and group_concat

I have data in a table like so: +---------+----------------------------------------------------+ | videoid | ips | +---------+---------------------------...
1
vote
1answer
21 views

Get the combined row count by month from 3 tables

Using Postgres 10+, I have three tables with similar structures, where I'm trying to get a count of rows grouped by months. I have a functional query working for two tables, as below: CREATE TABLE ...