Questions tagged [count]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
17 views

count and group_concat

I have data in a table like so: +---------+----------------------------------------------------+ | videoid | ips | +---------+---------------------------...
1
vote
1answer
22 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 ...
8
votes
2answers
763 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
0
votes
1answer
18 views

Join on unnested integer array column and count occurrences in array

I am trying to join two tables after I have unnested one of them. The tables are: CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60)); CREATE TABLE training_modules (id ...
2
votes
2answers
426 views

SQL script to count rows for all views in a database with SELECT-only access

I need to know the row count for every view in a third-party database that I have been given SELECT-only access to. The following script works for tables but not views: SELECT SCHEMA_NAME(A....
15
votes
2answers
17k views

MySQL - Difference between using count(*) and information_schema.tables for counting rows

I want a fast way to count the number of rows in my table that has several million rows. I found the post "MySQL: Fastest way to count number of rows" on Stack Overflow, which looked like it would ...
2
votes
1answer
43 views

join table and count them SQL based on segment

I have 2 tables and I'm trying to join and count how many people have answer each questions. I'm trying to find a better way to have only two column. the first one will be of people who answer "Just ...
3
votes
2answers
51 views

Query is faster with additional JOIN

SELECT COUNT(DISTINCT User_id) FROM Session GROUP BY Floor_id; SELECT COUNT(DISTINCT User_id) FROM Session JOIN Floor ON Floor.id = Floor_id GROUP BY Floor_id; The second query runs 20x faster ...
11
votes
2answers
67k views

Select only those records that have different/multiple values for a particular column

Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values: Member table ID ...
0
votes
0answers
24 views

Time wise user online count

I'm not a DB expert but i'm trying to get time wise user online count to show in a graph. I have manage to get online user count for current time but how to get for a time span ? i need each minute ...
2
votes
1answer
92 views

Improving performance of count where only result > 0 matters

I have following query: select count(*) -- rp.tag from submission inner join post rp on submission.id = rp.submission_id where user_name = ?1 and lower(rp.tag) like ('%verified%') and not lower(rp....
1
vote
2answers
50 views

Count results after filtering out

I am using MySQL 5.7 and need to count results after filtering out the data. My table structure: +----+---------+---------+---------+ | id | metakey | metaval | tour_id | +----+---------+---------+--...
0
votes
1answer
55 views

Calculating count on same table based on condition

So I need to calculate the amount of Clients who's LastName start with the same letter, then use that count as part of an Reference number. I.e. something like this: declare @...
0
votes
1answer
12 views

Help with optimizing Oracle Query - nested with count

I have 2 tables (Students and Cellphone). Columns in Students: StudentID, FNAME, PHONEID Columns in Cellphone: PhoneID, Phonename I want to create this: • Run a query to show the most popular ...
4
votes
2answers
350 views

Why does SAMPLE BLOCK often return zero?

In a comment on another question, someone asked: I used SAMPLE BLOCK in many tests but found that it would inexplicably return '0' much of the time. Any idea why? I speculated that this might be ...
19
votes
3answers
30k views

PostgreSQL using count() to determine percentages (cast issues)

I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0. select (count (refinst) / (select count(*) from ...
0
votes
1answer
48 views

Efficient way to check for number of records more than a limit from a table in SQL Server

I came across queries that can most efficiently get the total sum of the records in the table. However, i want to group the records in a table by a column and check if the count of the records is ...
1
vote
1answer
213 views

Count where any 3 columns have values (not null)

I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 ...
0
votes
3answers
75 views

SQL Join and Count

I have 3 tables in SQLServer that I want to join so I can count the number of times each SUBJ_NAME occurs for the ITEM_IDs in the LOAN table. CREATE TABLE SUBJT ( SUBJ_ID CHAR(4) PRIMARY KEY, ...
1
vote
1answer
72 views

How to count records from different tables [closed]

So I'm trying to filter some records out of my select inside a form on my website. Problem is that I currently have this setup: Database Table1 time Number Table2 Number Something I'...
1
vote
2answers
376 views

If count >= row then insert row (substitute for variables and else/if)

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense. So I've got a table that looks something like this: post_id | meta_key | ...
0
votes
2answers
40 views

For states (col) receiving exactly X number of items (row), sort the items by weight (col)

I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package. I am able to get the States with the exact number of packages, but don't know how ...
1
vote
1answer
31 views

Count Assets Barcodes and Group by Rooms

I have 2 tables Asset Register and LocationRoom. I want to count the number of asset barcodes in each room. I want the output to be the list of Asset Barcodes, Description,RoomNo and count for ...
0
votes
1answer
38 views

Issue with Maximo KPI Returning “0” Instead of a Calculated % [closed]

I am attempting create a KPI in Maximo that will count the work orders completed on time and divide it by the number of work orders due for the current month, then multiply it by 100 to return a ...
0
votes
1answer
24 views

Count Total Transaction each specific row even if 0

I have a 3 Columns namely: Payment Channel Payment Name Total Transaction The Total Transaction will be the COUNT of each Payment Channel and Payment Name. In the Payment Channel, values have PC01-...
1
vote
2answers
177 views

How can I count distinct columns inside a group?

I have the following query: SELECT DISTINCT name, surname, id_number, gender FROM table1; How do I filter by gender to get the gender breakdown of the results of the above query? Do I use a subquery?...
0
votes
5answers
287 views

Count rows where value differs

I have the following dataset in SQL Server: Name Pattern ------- ------- Peter DRF Peter DRF Peter BBB Peter DRF Peter CCC Peter CCC Sue DRF Sue CCC Sue DRF ...
0
votes
2answers
31 views

How to SELECT most common (viral) from a table and LIMIT results for pagination?

Sorry if it's a dubplicate but I can't find an answer to the question. Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is ...
0
votes
1answer
21 views

Get count of value row

I'm trying to figure out what MySQL code would do the following. Count * from 'users' table (eg; 0,7 rows) then within the count get the count of 'money' based on the value of the user. I'm ...
2
votes
2answers
473 views

how to count distinct value per day with start time and end time

my title may seem a bit confusing but here's the context. I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user ...
0
votes
1answer
49 views

Join 3 tables with where and sorting clauses

I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too. Each camera has a reference of vendor_model as model_id. 10 ...
2
votes
1answer
154 views

Get total number of rows when offset is greater than max

I have a table users that has, say, 42 records. To get the total rows and some other details, I use a window function: select count(*) over() as count, name, id from users And since the consumer app ...
1
vote
2answers
144 views

Count items on condition

In an application where a user can have copies of a mobile application installed on multiple devices I'd like to know how many iphone and how many android apps each user has. I tried a query such as ...
2
votes
2answers
642 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
24
votes
2answers
40k views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
0
votes
2answers
90 views

WHERE clause on a TIMESTAMP column causes serious performance penalty

I have a pages and a page_views tables with 100k~ & 4M~ records respectively. I run a query to get the top pages that have the most page_views: select `pages`.`id`, `pages`.`host`, `pages`.`...
1
vote
1answer
14k views

Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement

Within an Oracle procedure, after opening a cursor for a select statement, I fail to find a mean to count the number of rows fetched. OPEN mycursor FOR SELECT * FROM TABLE; -- mycursor%ROWCOUNT is ...
9
votes
4answers
15k views

show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table. In other words, can you think of a query to come up with something like this in mysql? +---------------------...
0
votes
1answer
254 views

MySQL Subquery count

I need to find how many resources are available in depot when transit arrives. I need to have transit name and resource count on result. I'm using MySQL. I wrote this but it gives me error when adding ...
0
votes
1answer
49 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
0
votes
1answer
37 views

mysql query to fetch group wise count

I am having mysql tables like below tbl_source_1 SRC_ID_1 | SOURCE_NAME_1 1 | ABC 2 | DEF 3 | GHI tbl_source_2 SRC_ID_2 | SOURCE_NAME_2 1 | NAME_1 2 | NAME_2 3 ...
1
vote
2answers
563 views

How can I get the percentage of the table the groups represent?

Given a query like this, CREATE TABLE foo(grp) AS VALUES ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('e'); I can use GROUP BY to aggregate into a,b,c,e, but how can I return the different ...
0
votes
1answer
97 views

JOIN tables and return count even if it is 0

I have this query: select settings.customer_id, current::jsonb->'alarms' as alarms, from settings join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res....
1
vote
1answer
324 views

Count all rows for each DISTINCT

How can count all rows of WHERE statement: SELECT DISTINCT column1 FROM table COUNT WHERE date::text LIKE '%2014%'; My output: column1 ======= info1 info2 info3 Desired OUTPUT: column1|...
1
vote
0answers
168 views

MySQL count rows of multiple tables for each user

I basically need this with some adjustments, I just can't seem to figure it out how to do it. I need to sum the rows of two tables, for example: SELECT student.StudentID, student.`Name`, COUNT(...
2
votes
1answer
53 views

where clause after limiting results

I have a question about getting results after limiting the rows. I don't have much experience with MySQL, but I'm trying to get results from a database where I need to do the where clause after ...
0
votes
2answers
59 views

Calcutation at SQL level

I have this query: SELECT * FROM ( SELECT COUNT(lead_status) as 'Pending files' FROM vw_file_registrationlist WHERE lead_status_id = "1" || lead_status_id="2" || lead_status_id="4" || ...
0
votes
2answers
65 views

Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products

I have table: brands brand_id name 1 Apple 2 Sony 3 Nokia 4 HTC products id brand_id qty 1 1 3 2 3 0 4 1 1 2 2 1 I need to ...
0
votes
1answer
43 views

Get two row counts from the same table [closed]

What I am trying to achieve is run both queries (below) at the same time. From one table rvsessions I need to get the count for the total amount of sessions(which is achieved by the number of times ...
0
votes
1answer
125 views

MySQL, would like to get both a field and a count inside GROUP_CONCAT

I can get a list of statuses that a tracking ticket has had, as well as the id of those statuses, as follows: SELECT t.id, t.name, GROUP_CONCAT( CONCAT(s.id, ':', s.name) ORDER BY s.id) AS ...