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
972 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 ...
0
votes
1answer
1k views

counting the number of a specific word in one SQL column

I'm trying to make a survey like a referundum to know if my neighboors in my small building are for or against a colectif compost to reduce the organics wastes. For this tool vote I'm using PHP and ...
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
858 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
227 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 ...
9
votes
1answer
531 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 ...
0
votes
1answer
2k views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
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 ...
0
votes
1answer
50 views

SQL query with multiple many-to-one joins [closed]

I want to select a count of all surveys where the survey.property.address.city == "Garrison". I have the following tables (stripped for your convenience): surveys properties ...
4
votes
3answers
715 views

How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function). But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy ...
1
vote
1answer
991 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(...
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, ...
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 ...
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
8k 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
18k 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 ...
37
votes
1answer
40k 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 ...
5
votes
2answers
24k views

Distinct Combination of Two Columns

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other. The data is all around backup policies being run on against ...
3
votes
1answer
53 views

Extra “count” row with total of 0

Consider the following: db=> SELECT ga, count(ga) FROM gb GROUP BY ga ORDER BY ga ; which gives the following output: ga | count ---+------ ? | 200 A | 100 B | 50 | 0 (4 rows) Why ...
3
votes
1answer
925 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 |...
2
votes
2answers
2k views

Trying to get count of childs rows

I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs. select c.id, c....
1
vote
2answers
8k views

MySQL - trying to combine `GROUP_CONCAT` with `COUNT()`

I am trying to combine GROUP_CONCAT with COUNT(), and failing. I have the following data: CREATE TABLE `tab1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `team` varchar(45) DEFAULT NULL, `...
1
vote
1answer
1k views

Count childs of parent with sql or save in column?

In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this: WITH RECURSIVE ...
2
votes
1answer
3k views

Speed up COUNT(*) when using an index

I have a table similar to this (simplified): CREATE TABLE books ( id INT AUTO_INCREMENT, category INT NOT NULL, PRIMARY KEY (id), KEY (category) ); This table has over 10,000,000 ...
1
vote
2answers
65 views

group result of multiple table to get unique result after count operation

survey_categories +---------------+-------------------+ | survey_cat_id | survey_cat_status | +---------------+-------------------+ | 1 | 1 | | 2 | ...
11
votes
2answers
66k 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 ...
3
votes
1answer
3k views

COUNT number of column values with INNER JOIN [closed]

I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle: http://sqlfiddle.com/#!15/6ce465/5 So basically I have two tables, responses and questions. The structure is responses ->id ...
0
votes
1answer
53 views

Group-by in an Oracle database

on an Oracle database. If I make an: select id,id_operator,denumire,cod_fiscal,loc_den,str from rap where is_punct_lucru=1 and id_operator in ( select id_operator from rap where ...
4
votes
1answer
1k views

Get the would-be row count without running twice

So I have a stored proc that utilizes a paging function to only return a subset of the query along with the actual total row count. In other words, it takes parameters for the start and end rows to ...
2
votes
1answer
38 views

Query to get number of items from different table reference in another table [closed]

I have 5 tables which are - Project(PK Project_ID), - Bottles (PK Bottle_ID, FK Project_ID), - Plastics (PK Plastic_ID, FK Project_ID), - Glasses (PK Glass_ID,FK Project_ID) and - Cups (PK ...
-1
votes
3answers
790 views

Query to return zero entries with COUNT

I have a problem with one task. I need to display various information through two diferent queries.(or one if it could be possible) The table has these fields. Location is set to x +------------+-----...
1
vote
2answers
3k views

Count of distinct values of a table using sql

name number ============= lin | 8 lin | 8 lin | 8 rob | 8 rob | 8 rob | 8 lin | 8 lin | 41 lin | 41 rob | 41 and I want the result like name | Count of 8 | count of 41 ====...
0
votes
1answer
4k views

Grouping based on the status, then count its product and the total based on its category ID

I have a table with product ID, category ID and status of the product. +---------+-------------+-------------+ | id | cat_id | status | +---------+-------------+-------------+ | 1 ...
3
votes
1answer
4k views

How to get row count from all tables of a schema ( without using information schema )

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
1
vote
1answer
4k views

mysql: counting number of tickets which are open per day basis

I have a table tabName with the following rows: id(PK) .. Created(datetime) Resolved(datetime) Status(new/resolved/open) 1 2015-05-10 00:00:00 1970-01-01 .. open 2 2015-...
4
votes
2answers
40k views

Join two tables and return data and count in a single query

I have two tables in an SQL Server database, one with two columns and one with four: tbl_email_list email_list_id int (PK) email_list_name varchar tbl_email-details email_uniq_id int (PK) ...
0
votes
1answer
129 views

Inaccurate count when using multiple count functions in PostgreSQL

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.890 I'm relatively new to SQL and am learning a lot of about writing queries as in ...
0
votes
1answer
2k views

Add '%' sign to MySQL results

I am using a query to calculate total percentage but I want a '%' symbol to appear next to it. Any ideas that could help me My query so far is SELECT student.StudentID, student.`Name`, ROUND (Count(...
1
vote
2answers
1k views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) -----------...
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
1k views

oracle database - sql for counting the columns with the same id with the one in current row

I have an oracle database and a few tables and with this query: select rap.DENUMIRE,a3.ID,op.FK_ID, (SELECT COUNT(*) FROM ANEXA3_TABEL1_ROWS) AS count from ANEXA3_TABEL1_ROWS a3 inner join ...
2
votes
1answer
8k views

Postgres - count of GROUP BY

I am trying to determine the total number of contributors in an image database who have contributed at least five images. The following query will give me counts of images by contributor, but that's ...
2
votes
1answer
668 views

MYSQL count the number of members in a group

Recently started working with MySQL and PHP so my setup is still a bit clunky most likely but here is how my 2 tables look right now: Table 1 named clanlist Table 2 named clanusers What I need to ...
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 ...
1
vote
1answer
1k views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by "...
1
vote
0answers
1k views

Limit relation one to many or one to one

In a relationship one to many or one to one you can limit the relationship of the related table? For example, I have a table 'cliente' and another 'acct', each 'cliente' can have thousands (perhaps ...
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 ...