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
0answers
842 views

count rows with same value and get column from other table

I'm trying to count product numbers for a period of time there are three tables. invoice (table) Column Column ------------------------------------------- |invoicenumber ...
1
vote
2answers
121 views

MySQL Count where all conditions evaluate to 1

What is the best way to get count for all the records, where case evaluates to one for all the conditions. I have the query as: SELECT uuid , COUNT( CASE WHEN (( dt = 1 or level0 = '...
2
votes
1answer
3k views

Grouping results with left and inner join with zero counts

I have a query like SELECT `campaign_question_options`.`text`, COUNT(`campaign_submission_answers`.`answer`) as `count` FROM `campaign_questions` INNER JOIN `...
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 ...
3
votes
3answers
10k views

Count multiple tables as one count

I have seen how to do multiple counts for different tables, but never how to combine them all into one. I have a MySQL DB where I am running the following query: SELECT characters.name, COUNT(*) AS ...
1
vote
3answers
501 views

Help with COUNT across multiple columns

I've had a look around and a play and can't what I need to work so wondered if you could help? I have four rows in a table and I wish to count the number of items across all four. So to make it ...
1
vote
0answers
1k views

I can't divide 2 count(*) values without returning zero

The statement I'm using is for a KPI in Maximo and I currently have this statement: select (select count(*) from incident where incident.slanum = 'USERADMIN' and incident.actualfinish > incident....
4
votes
2answers
1k views

How to get COUNT of a range between 2 columns

Table: Staff | StartingTime | EndingTime | starthour | endhour ------------------------------------------------------------- 10 | 0813 | 1610 | 08 | 16 20 | ...
2
votes
1answer
4k views

Count rows in dataset SSRS2012

I want to count the number of rows in my result DataSet, I put in my DataSet a calculated field with this expression : =CountRows("DataSet1") But I have the following error message: "The expression ...
0
votes
2answers
121 views

SQL Performance: COUNT or extra attribute?

If I want to count the number of a multivalued attribute, which way is faster: a) using COUNT(attribute) on a whole table or b) adding an extra attribute which counts these stored values (to be ...
0
votes
0answers
123 views

Paging via Number of Result Record vs COUNT(*)

I have been working on fixing a piece of code for a customer that has been very slow for them. I have made that fix, and thats all lovely, but I noticed something odd in the paging. When the query ...
3
votes
1answer
35k views

Store the resulting list of a SELECT in a variable to count AND use the result

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the COUNT(...
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
1answer
64 views

For each record, count records dated earlier in PostgreSQL

I have a table that contains a column year. I need, for each row, calculate the number of entries where the year is less than that in the row. So that the output would look like: col1 | col2 | .....
0
votes
1answer
106 views

COUNT() and link between tables

I have some difficulties to make a query. Basically, I have a 3 tables: member, build and up: member --+--- id|... build --+-----------+--- id|member (fk)|... up --+-----------+--------- id|member ...
1
vote
2answers
4k views

How to count the rows from 1 table but from two different column

SELECT distinct Major, COUNT(Major) AS MajorCount FROM [College Management System].[dbo].[ApplicantCourseDetails] group by Major SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM [College ...
7
votes
1answer
14k views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
2
votes
1answer
361 views

Select the max from two max values

I need to find the airport(departure or arrival) that had the most passengers in the last year. The tables are: PASSENGER(id,name,surname) TAKES(id_passenger,id_flight) FLIGHT(id,departure_id,...
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? +---------------------...
1
vote
1answer
74 views

join from different tables gives same result, but it shouldn't

This is the code I'm using and I'm having a problem with it. SELECT `disenos`.*, `votos`.`valoracion`, `usuario_contacto`.`nombres`, `usuario_contacto`.`apellidos`, `users`.`username`, `users`.`...
0
votes
0answers
36 views

Need separate columns that calculates percentage [duplicate]

I have the following table: NAME ------ Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage ------------------------- Alex 2 33% Bob 2 33% ...
3
votes
6answers
29k views

Need separate columns that calculate percentage

I have the following table: NAME Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage Alex 2 33% Bob 2 33% Roger 1 16.6% Tim 1 16....
0
votes
1answer
232 views

Count(*) as rolecount, filter by rolecount = 1

How can i filter out the rows who only got rolecount = 1? WHERE rolecount = 1 gives an error. Can't really figure out how to do it. SELECT u.uid AS uid, u.name AS name, u.picture AS picture, n....
3
votes
1answer
557 views

Simple SQL Query

I have a db that in one column have 5 possible values: "Fechado", "Aberto", "Em Despiste", "Cancelado", "Resolvido". And im trying to do a query that counts the values to a json file var data = "...
1
vote
2answers
126 views

Merge output of two Queries MYSQL

I have a mysql table with the following schema: +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+---...
2
votes
1answer
7k views

Combining count queries into one table row

I'm banging my head against the wall because I'm sure this is super easy. I have a few queries set up that get counts for a few related tables. This is for an arcade game tracking system. The ...
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 ...
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
59 views

How to optimise very simple query with subquery that only needs to execute once

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING COUNT(...
20
votes
6answers
4k views

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
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 | ------------------...
2
votes
2answers
1k views

Count where previous record exists in either of two tables

I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another. The table that I'm sampling has a composite PRIMARY ...
2
votes
1answer
163 views

How to efficiently count rows in a query by limiting the number or found rows?

I have a website where I output a pagination with a limited number of pages, let's set it to 50 for example. That is, if there are actually 51 or more pages of content the 50th will be the latest that ...
3
votes
2answers
8k views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON (`Business`...
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
2k views

How do I group the data in the following format?

Using the following table OrderDate OrderID [dates] [id] I want to display in the following format : quater name nooforders q1 10 q2 10 q3 20 I have ...
0
votes
2answers
2k views

What is the difference between COUNT(*) AND COUNT([attribute]) in the following scenario?

I have the following tables : DEPARTMENT department_id department_name manager_id location_id and EMPLOYEES employee_id first_name last_name email phone_number hire_date job_id salary ...
2
votes
1answer
2k views

mysql optimize group by having count

I have 2 mysql table: table t1: CREATE TABLE IF NOT EXISTS `t1` ( `id` bigint(20) NOT NULL, `url` varchar(255) DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `type` varchar(255) DEFAULT ...
1
vote
1answer
868 views

Compare Number of Rows of Tables

I have two or more tables different by structure and I want to compare their numbers of rows, which I get with the count-star query: select count(*) from table_a For two tables I would like to see ...
1
vote
1answer
608 views

The most efficient method to obtain the total entries into a mysql table

I have a table that has a very high usage (insert/update/select) - one of the operations is to obtain a count of the number of rows. So far I've been using the classic select count(pk) from table but ...
1
vote
2answers
241 views

Can counting rows be made any faster?

I need to calculate the total number of rows based on a set of criteria: SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a'; The above query takes 0.10 sec to run on a MyISAM table with ...
1
vote
1answer
21k views

Using count in triggers to update another table

Say i have a table called trx_request with the following data id | confirmed(TIME) | finished(TIME) ------------------------------------- 1 | 12:05:00 | NULL 2 | 13:11:00 | NULL and ...
1
vote
1answer
87 views

Counting number of visitors who did NOT log in

I'm trying to calculate the number of visitors to the homepage of my site who did NOT then log in to the member's section of the site, while also filtering bots and admin users. Bots and admins are ...
2
votes
0answers
420 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The query ...
1
vote
2answers
10k views

MySQL count multiple columns and sum the total occurrence

I have the following table, say, MyDRAW ... DRAWNO PRIZENO FIRSTD SECONDD THIRDD FOURTHD M123/14 8458 8 4 5 8 M123/14 7214 7 2 1 4 M123/14 1640 1 6 4 0 ...
1
vote
1answer
80 views

SELECT showing two COUNTs

I had this table: tblA id | name | value 1 nameA 1 2 nameA 0 3 nameB 0 4 nameC 0 5 nameC 1 6 nameC 1 As you see, Value can be 1 or 0 I need to perform a query ...
1
vote
1answer
2k views

Count records in a related table

I have three tables: items, members, subscriptions. One item can be get by 1 or more members (one member can get more items), so subscriptions represents the many-to-many relationship between items ...
3
votes
2answers
12k views

Mysql match on two columns and get count

How do I get a count of? +---------+-------------+ | col1 | col2 | +---------+-------------+ | Joe | April | | Fred | May | | Sam | April | | Joe | ...
1
vote
1answer
417 views

Retrieve title from rows with a maximum count in other columns

Given the following table archived: CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id ...
13
votes
1answer
4k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...