Questions tagged [count]

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

Filter by
Sorted by
Tagged with
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
306 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
77 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 ( ...
1
vote
1answer
987 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
484 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
55 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
47 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
92 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: ...
5
votes
2answers
6k views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
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
11k 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 ...
9
votes
3answers
36k views

Count null and not null values in a column

How to count and retrieve null and not null on same column in MySQL? mytable --------------------------------------------------- id | name | visited | registDate | ------------------...
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
488 views

ORDER BY count and Bitmap Heap Scan

I have a table with hits: Column | Type | Modifiers --------------+-----------------------------+--------------------------------...
0
votes
1answer
23 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 ...
0
votes
2answers
2k views

Count values of a column

I've got two database tables: Persons id user product 1 1 3 2 1 5 3 1 7 products id kilo 3 8 5 15 7 3 I'm wondering how to count values from a ...
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....
6
votes
4answers
5k views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on '...
0
votes
1answer
52 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
461 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
990 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 ...
2
votes
2answers
17k views

Get count based on 15 minutes of interval

I have a table containing Call records: tbl_calls cl_Id cl_StartDate cl_endDate I am passing two parameters @StartDate and @EndDate to my stored procedure. My requirement is to get the count of ...
0
votes
0answers
52 views

Efficiently counting Clicks grouped by Week as well as all sub-totals

I have a large and slow database 'LargeSlowTable' which records clicks by users. I only have read permission, not write, so I cannot use Global Temporary Tables. I want to count the number of clicks, ...
5
votes
1answer
865 views

Fastest query for distinct IDs in a many-to-many relationship

I have this table in PostgreSQL 9.4: CREATE TABLE user_operations( id SERIAL PRIMARY KEY, operation_id integer, user_id integer ) The table consists of ~1000-2000 different operations ...
1
vote
1answer
228 views

Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes? If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate ...
0
votes
1answer
55 views

How do I get only those values that has related elements from other table?

I have three tables for a social network: companies, users and countries. Users belong to Companies and Companies have a Country. All tables have their own id column. Table users has a company_id FK ...
5
votes
2answers
5k views

COUNT(*) gives more than 1 with LIMIT 1?

I'm trying to count old records. Why does Postgres give a result of 1160, even though I set some limit, LIMIT 1 in this case? SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT ...
9
votes
1answer
532 views

Why SELECT COUNT() query execution plan includes left joined table?

In SQL Server 2012 I have table valued function with join to another table I need to to count number of rows for this 'table valued function'. When I inspect the execution plan, I can see the left ...
1
vote
1answer
80 views

Optimization of complicated sums and counts in related tables

I have a categories table, which have two columns, id and name. I have a types table, which have three columns, id, category_id and points. I have a products table, which have three columns, id, ...
1
vote
1answer
1k views

Alternative query to avoid count(distinct)

i have a performance issue with my query and i need to rewrite it to have my result in better time. If you can help me with an idea? My query is the following: select dc.Country, COUNT(...
2
votes
3answers
2k views

Get count estimates from pg_class.reltuples for given conditions

Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
1
vote
1answer
307 views

“No Current Record” when using COUNT() in Access 2010?

Okay, so, I'm trying to get a count of how many customers each service provider has billed for over the last year or two. Our database's data is slightly imperfect, so I'm trying to kludge together a ...
0
votes
2answers
46 views

Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example: A: ...
1
vote
2answers
9k views

Multiple COUNT fields, GROUP BY

I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries? I'm aware of WITH ...
1
vote
1answer
19k views

2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

Ref : https://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql I got answer (thanks coytech ) but i need one more column in its : as per : id | mid | pid | owgh | nwgh | ...
0
votes
1answer
812 views

Count distinct values of a column and insert them to another table

So, I have a database for logs and statistics. But, the amount of data sent is too high, so I was thinking of grouping the data into another table. So the structure of the original Database is: ID ...
38
votes
1answer
41k views

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table inner ...
3
votes
1answer
934 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 |...