Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [count]

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

0
votes
0answers
17 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 ...
0
votes
1answer
36 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
10 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 ...
0
votes
3answers
47 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
2answers
37 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 | +----+---------+---------+--...
1
vote
1answer
54 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'...
0
votes
2answers
30 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 ...
0
votes
1answer
20 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 ...
1
vote
1answer
22 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 ...
1
vote
2answers
46 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
1answer
15 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
20 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-...
0
votes
2answers
29 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
15 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
233 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
46 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
48 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
46 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 ...
0
votes
2answers
57 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
2answers
82 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
1answer
39 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 ...
0
votes
1answer
55 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
14 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
33 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
100 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
37 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
83 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
65 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
34 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
1answer
43 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 ...
0
votes
2answers
43 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
33 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
37 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 ...
1
vote
2answers
74 views

SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause

I have a MySQL query: SELECT p.informationProvider as pGLN, p.productID, p.productStatus FROM tblproducts AS p WHERE -- informationProvider is of type varchar(14) p....
-1
votes
2answers
53 views

MySQL Query can we count rows using php to reduce time of count(*) in a query or there is no difference?

Having the following queries, where the first query is returning a field of number of rows found, and the second returns the actual rows: First Query: SELECT count(*) as total FROM individual i ...
0
votes
1answer
18 views

Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column [closed]

I want to create a query that will search the table and find the following: If a POL_N entry has ENDO_N entries then the COUNT ENDO_N field will start counting the ENDO_N entries and will get the ...
0
votes
1answer
19 views

select data from related tables and display their counts in the relating table

I have a table which stores data about an inspection. Inspections are carried out in a particular company belonging to an industry, located in a region. the inspections table has the industry_id field ...
-1
votes
1answer
28 views

Count comma seperated values in mysql ORDER BY clause

I have a table name products: id - pro_name - ip - date_created 1 - abc1 - 27.106.121.23,27.106.121.86 - 2018-08-15 13:32:00 2 - ...
0
votes
2answers
60 views

Calculate ticket sales by using SUM in SELECT clause

In this ticketing setup on dbfiddle, I am trying to calculate the ticket sales for a particular event by multiplying the ticket price and the total number of tickets sold for that event, based on ...
2
votes
0answers
40 views

MariaDB running indefinitely on inner join with count

I am currently experiencing a very, very weird issue with a query in MariaDB. Our server experiences a "504 Gateway Timeout" quite often after a database upgrade, and I was investigating the issue. ...
1
vote
0answers
666 views

How to apply a condition on “count over partition”?

I'm using Oracle 11g. Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit. With this table: datetime_id | user_id | place_id and I want to ...
0
votes
3answers
111 views

SQL Count function returning inaccurate results

I am trying to write a query in oracle to list the number of records contained in a single table filtered by multiple columns in the tablespace. Here is the query syntax I have come up with: SELECT ...
3
votes
2answers
154 views

MySql concurrency with COUNT

I Have a table with user_id and product_id. For my purpose, a particular user is allowed up to 5 rows only. To enforce this, I created such a SQL statement: INSERT INTO the_table (product_id, ...
2
votes
1answer
525 views

SQL query to get the user, counting the minimum number of records

I have an table with some records and a column LAST_EDIT_USER. If I want to select all users with the number of appearances for every user, I would do SELECT LAST_EDIT_USER,COUNT(LAST_EDIT_USER) ...
1
vote
2answers
37 views

Basic question about grouping and counts

I'm having trouble coming up with a query in MySQL. Suppose we have a table that logs an action by a user. User1 is a Doctor and is logged several times. User2 is a Doctor and is logged once. User3 ...
1
vote
3answers
579 views

SQL Server COUNT() seems to be ignoring WHERE [closed]

I am trying to set up a SQL Server query that lists the count of people signed up for tours during a conference event and then displays the total sum. This is my SQL code that is coming close, but it ...
-2
votes
1answer
460 views

multiple counts of the same column SQL

currently using the following SQL to get multiple counts from the same column but my results are returning all the same SELECT act.OWNER_LOGIN, COUNT(CASE WHEN act.X_ACTIVITY ='Call' THEN 1 ELSE 0 ...
3
votes
3answers
117 views

Index scan on UNIQUE index for count(*)

I've got a table t with ~23 million rows (4248 MB in size). There's a column row_id in it, with a not null constraint. And a unique index p1 on t(row_id). When I do select count(*) from t to count ...
0
votes
4answers
56 views

Help for grouping a field and taking total sum

I have a sorted data in a table F1 F2 F3 F4 A M X 1 A N Y 2 A P X 3 B M X 3 B N Y 1 B P X 5 but I want the sub count of values in F1 column for every row. ...
0
votes
1answer
33 views

SQL Server count value of column [closed]

I call the table is [vDemo] []1 i want count MaLC by TenNV, result is if record 13 MaLC is L then Count(x) = 16, Count(L) = 1