Questions tagged [count]

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

Filter by
Sorted by
Tagged with
6
votes
3answers
290 views

How to count the number of rows by datetime field with the division into time intervals

I'm using Oracle Database 10g. There is a table with information about clients: +----+------+---------------------+ | id | name | registered_at | +----+------+---------------------+ | 1 | ...
2
votes
1answer
2k views

how to sort by timestamp field while counting duplicates

I have a table with username, timestamp and action columns. I need to retrieve a list of usernames, total occurrences of each user and the latest timestamp for each user. The following statement works ...
1
vote
3answers
83 views

How to add a summary count to this query

I am using Postgres 9.4 and I have a query that looks like the following: SELECT attendees.event_id, attendees.user_id, users.tags FROM users, attendees WHERE 'crystal:promo' = ANY(users.tags) AND ...
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 ...
2
votes
2answers
8k views

Aggregate interval data to hourly

I am getting data for 15 minutes interval. I would like to aggregate this data to hourly.Can anybody advise how should i do this query to sum the interval values and display it?
3
votes
1answer
3k views

Speed up count queries on a couple million rows

Assuming a db full of products. A product can belong to exactly 1 collection and is created by a user. Rough scale of the db: Products: 52.000.000 Collections: 9.000.000 Users: roughly 9.000.000 as ...
1
vote
2answers
50 views

Two separate column counts grouped by one of those columns

I have a Voters table, which has fields including party and email. party takes just a few acceptable values. I have a query which counts party membership and groups by the party: SELECT party as ...
3
votes
2answers
1k views

count null values of table from information_schema tables

Is there any way to count null values in a column by using information_Schema tables? EG: SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='myschema' and ...
14
votes
2answers
16k 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 ...
3
votes
1answer
1k views

Getting duplicate results in many to many query

I have designed a bookstore app with these tables. There's a book with an M2M relation to author. Lastly, there's table reader which keeps track of which user has read the book i.e. an entry in the ...
14
votes
1answer
11k views

Why is this sqlite query much slower when I index the columns?

I have a sqlite database with two tables, each with 50,000 rows in, containing names of (fake) people. I've constructed a simple query to find out how many names there are (given name, middle initial, ...
0
votes
1answer
38 views

Mysql query to get count result and group by

I've got three tables with different records for users, User username | realname | date evn-az-3ju john 11/2012 03:09:40 p.m. jwyvm_rdyt steve 12/2012 ...
1
vote
1answer
578 views

Select count question with where clause

I have a initial table where I have the following rows: ID_0, geom and utilisatio (where the value varies from 1 to 7). I am trying to create a table where I would have the count of a specific ...
5
votes
3answers
2k views

MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed

I benchmark DBs to find out the best for my project and I found that count(*) is extremely slow in PostgeSQL. And I don't understand is it a normal behaviour of PostgeSQL or I do something wrong. I ...
0
votes
1answer
2k views

Select countries and counts of people per country

I have 2 tables, one is a list of countries (id, country) and another is the users (where the country column is the id of the country in the countries table). Is it possible to make a single query ...
3
votes
2answers
229 views

T-SQL Count in single select

I have a table called Catalog. It has multiple columns, for example CreatedByID and ModifiedByID that contain user IDs. I would like to get count of how many documents a user has created and edited. ...
0
votes
1answer
102 views

SELECT records based on return of COUNT query

I would like to create a query that returns records if values in a column occur more than once. I came across other Stack Exchange questions but these only dealt with returning the number of ...
1
vote
1answer
33 views

Find addresses shared by more than one customer_id where customer_name is is distinct

I need to return the list of addresses that have more than one customer_ID associated with them, exclude the customer_IDs that have the same customer_name and return the number of time the address ...
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 ...
1
vote
1answer
19 views

Selecting entries with the same count as in another table

I'm using a SELECT statement to select multiple IDs and than use GROUP BY ID HAVING COUNT(*) = someNumber to only have the IDs with a certain count. What I however actually want to do is to select ...
0
votes
1answer
98 views

Group COUNT by month less than variable

In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus: CREATE TABLE [dbo].[Employee] ( [employeeID] [int] IDENTITY(9500,1) NOT NULL, [firstName] [nvarchar](50) ...
1
vote
3answers
298 views

COUNTING results per row in self referenced table

I have a table called Clients. And at this table, I have an idFather to know who added this row to the table. Sample: id | user | idFather | createdAt ---+---------+----------+----------- 01 | ...
2
votes
1answer
76 views

How to UPDATE by COUNT from JOINed tables

I have a schema as CREATE TABLE companies ( company_id int(11) NOT NULL AUTO_INCREMENT, company varchar(255), projects int(11), PRIMARY KEY(company_id) ) ENGINE=InnoDB CREATE TABLE employees ( ...
2
votes
3answers
460 views

How to merge multiple query results into one

1. SELECT COUNT( * ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name 2. SELECT SUM( inscan ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '...
1
vote
1answer
964 views

SELECT count(*) from a materialized view with unique index is slow

I have a materialized view with a unique index (on the id column) and both of these queries are slow (they scan the whole table): SELECT count(DISTINCT a.id) from the_view a; SELECT count(*) from ...
1
vote
1answer
97 views

Repeat total count value

I have a table called paths that looks like this: id parent_id -- --------- 1 null 2 1 3 2 4 3 5 4 6 5 7 5 8 5 9 ...
1
vote
1answer
472 views

Count approximation with where clause

I have the following problem, we have a table of facilities for apartments that looks something like this: I would like to perform the following query SELECT crawled_name, count(f.id) AS count ...
1
vote
1answer
37 views

ORACLE: Changing the grouping of a data to be based on month breakdowns

To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy Here is a example of the script, and screenshots of the output file This is ...
0
votes
1answer
54 views

Writing a query to COUNT all the services involving a particular

Learning PHP and SQL on an online MOOC, this is a homework that I've got to do. I need to write a query in SQL to count all the services which involve a particular person in my database. The ...
0
votes
1answer
46 views

Get distinct count from row and display in sepearte column

I am trying to get count of section type for each section id and course id My sections table ╔════╤════════════╤═════════╗ ║ id │ course_id │ name ║ ╠════╪════════════╪═════════╣ ║ 1 │ 1 ...
0
votes
1answer
87 views

MySQL - A Count with INNER JOINS - that multiplies the result by 8

The following SELECT with INNER JOINS works, but does not count it right. I have a tests table: Tests TestNumber (int primary key) TestComplete (int 0 = not complete, 1 = complete) ...other stuff ...
1
vote
3answers
2k views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
0
votes
2answers
2k views

need help joining two tables with a having count clause

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to ...
0
votes
1answer
2k views

Multiple counts in a SQL statement

Is it possible to have more than one count in a SQL statement such as: SELECT student_id, ( COUNT (ATTENDANCE_ID) from attendance where attendance_id IN ('ABSENT') ...
0
votes
1answer
238 views

Oracle : Is my DISTINCT function working properly?

I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. ...
2
votes
1answer
2k views

Schema name, table name and row count

My database has 50+ schemas. Each schema has around 100 tables and some views. I want a query that tells me the schema name, table name, and total number of rows in each table. The below query: ...
3
votes
1answer
1k views

Speeding up a GROUP BY, HAVING COUNT query

I'm trying to speed up this query in Postgres 9.4: SELECT "groupingsFrameHash", COUNT(*) AS nb FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876" GROUP BY "groupingsFrameHash" HAVING COUNT(*) &...
7
votes
4answers
10k views

MySQL COUNT(*) performance

I have a table with more than 15m rows. I need the total number of rows. So: SELECT COUNT(*) FROM thetable; Which takes around 50s to complete. Explaining gives me Select tables optimized away. I ...
1
vote
1answer
2k views

Count instances of value from one column in another column located in another table

Let's say I have the following tables : Table 1 task | date 100 | 2016-01-01 101 | 2016-01-01 103 | 2016-01-01 Table 2 task2 | date 113 | 2016-01-01 113 | 2016-01-01 103 | 2016-01-01 ...
3
votes
1answer
475 views

ORDER BY count and Bitmap Heap Scan

I have a table with hits: Column | Type | Modifiers --------------+-----------------------------+--------------------------------...
0
votes
1answer
22 views

Incremental column depening on column value

I would like to have a column which has an incremental integer depending on the column value after sorting the table. E.g. after sorting by state: Nr | State | Counter -------------------------...
2
votes
2answers
1k views

Creating calculated column

I want to create a rating weight and I need do calculate AVG, then find COUNT and multiply both to get weight. I have something like that: SELECT post_id, COUNT(CASE WHEN vote > 0 THEN vote ELSE ...
2
votes
3answers
11k 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 |'...
0
votes
1answer
57 views

MySQL query too long to execute

I have one query that work pretty well: SELECT [MANY THINGS] FROM vehicule v0_ FORCE INDEX (PRIMARY) INNER JOIN categorie_vehicule c1_ ON v0_.idCategorieVehicule = c1_.idCategorieVehicule INNER ...
0
votes
1answer
113 views

Count from a count (distinct)

I have a query like this: select * from questions as q left join question_filters as qf on q.id = qf.question_id left join users as u on q.user_id = u.id left join trainers as t on q....
0
votes
1answer
51 views

MySql remove counted value from another count

I'm trying to remove a counted value from another counted value but I can't get it to work. I'm using MySql This is my SQL: SELECT `GameID`, count(case `Liked` when 1 then 1 else null end) as ...
1
vote
2answers
2k views

UPDATE only if COUNT from another table is not zero

I update a table by counting the FK from another table as UPDATE table1 SET number = number + ( SELECT COUNT(*) FROM table2 WHERE table1.id=table2.id ) table1 is a big table, and I want to make ...
0
votes
0answers
63 views

How to use COUNT() properly with a 3 table join

I have a database where I have driver details, their assigned routes and their stop locations. For each driver on the database I need to find the location (or locations, as it could be more than one) ...
0
votes
0answers
458 views

Slow count using postgresql?

I have a query that is executed frequently with a few of the same conditions EXPLAIN ANALYZE SELECT COUNT(*) FROM "comments" WHERE "comments"."published" = 't' AND "comments"."user_id" = 15 AND (...
4
votes
1answer
969 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items attached ...