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
2answers
243 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
418 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 ...
1
vote
1answer
885 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 ...
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
617 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 ...
0
votes
1answer
1k views

migration from mysql 4.1.0 to mysql5.6.2

I have been trying to backup data with structure from my old legacy server to my local server for modifications. PhpMyAdmin timed out at the time of export, MySQL Workbench has performed connections ...
1
vote
1answer
22k 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
421 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
1answer
81 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 | ...
2
votes
1answer
1k views

How to UPDATE a column by COUNT of FK from another table?

How to extend this query for the entire table, to update all ids? UPDATE table1 SET number=( SELECT COUNT(*) FROM table2 where id=1 ) WHERE id=1 id is the PRIMARY KEY in table1 and FK in table2. ...
0
votes
1answer
143 views

Trying to count multiple values and total

SELECT COUNT (DISTINCT competition.genre_id) genrecount, COUNT(DISCTINCT band.band_id), genre.name, genre.genre_id FROM genre, competition_band, competition, band WHERE genre.genre_id = ...
0
votes
1answer
783 views

Join and Count based on Specific User ID in SQL

I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar ...
0
votes
1answer
26 views

Basic metric information for views

I would like to output some basic metric information about my MySQL database. Such as the names and number of rows for all tables and views in a given database. Tables are no problem as I simply ...
1
vote
1answer
20k views

SQL Limit + count group?

I'm tracking all the visitors of my site using this table: Visitors table In my admin panel, I have an overview of which countries visited my site, where they came from, ..., but because over 20+ ...
2
votes
1answer
617 views

select count(*) in mysql 5.5 innodb— rewrite advice?

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow... select count(*) from mails3 join questions using (question_id) where ...
1
vote
2answers
14k views

How to select rows which have max and min of count?

I have the following table in SQL Server 2012: CREATE TABLE [MyTable] ( [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(), [MyGroup] INT NOT NULL ); I want to output a table of the ...
0
votes
3answers
78 views

find sold subscriptions from table 1 and store name in table 2, joined by store id. (oracle)

I am trying to get the number of sold subscriptions from one table and the store name from another. The tables have a common column containing a store id, however the columns do not have the same name....
3
votes
1answer
2k views

Trigger for updating a count of rows from a table into another table

I have these two tables: mysql> select * from orders; +-------------+---------+ | Customer_ID | Item | +-------------+---------+ | A01 | CPU | | A01 | Monitor | | A02 ...
-2
votes
2answers
5k views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...
3
votes
2answers
780 views

Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records: SELECT COUNT(id) shareLikeNo FROM ...
0
votes
2answers
3k views

Counting results for cross join

How I can get count results with cross join? Example cross join: SELECT t1.firstname, t2.lastname FROM table1 t1 CROSS JOIN table1 t2
1
vote
1answer
1k views

Select Count(*) FROM with-clause-named table order by a, b not working

I have a series of tables defined in a stored procedure to eventually return to the application like this, then culminating to a simple table which contains distinct values from a previously defined ...
2
votes
2answers
269 views

Joining 4 tables and 2 counts

I have four tables. UserMaster ---------- UserKey - PK UserName UserID - UNIQUE UserLogTransaction ------------------ UserID - FK:UserMaster.UserID LoginTime LogoutTime CallCenter ---------- ...
1
vote
1answer
1k views

My SQL server showing different result with same query execution at different time

When I execute below query from MySQL server, it shows me different output for each time even though database is stable and not used by anyone except me. SELECT TABLE_NAME , table_rows FROM ...
1
vote
1answer
33k views

Need to count records and group count by date on Oracle

I have a table like the following ID created sent type ----------------------------------------------------- 0001463583000051783 31-JUL-12 1 270 0081289563000051788 01-...
9
votes
4answers
15k views

What can speed up a SQL count query?

When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems? I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so I'...
0
votes
1answer
89 views

Problems with generating Audit SQL

Im currently working on a project that uses Oracle 10g where every action from the user is recorded in a auditlog table. The current table holds the following values: AuditID AuditDate Username Page ...
2
votes
1answer
361 views

DB2 Count problem

We have a query that returns over 20,000 rows on some occasions but times out due to the large amount of data. We have written a COUNT(*) query to throw an error if the record count is more than 20,...
1
vote
1answer
48 views

Order questions depending on answer votes

I'm really curious about how to create an SQL query to order questions depending the votes_up and votes_down. (The votes can be the sum of both, votes_up + votes_down.) Question has_many answers ...
19
votes
3answers
7k views

Why doesn't InnoDB store the row count?

Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row ...
5
votes
1answer
327 views

I was interrupted while transferring a very large table. How can I pick it up from where it stopped?

I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the ...
8
votes
1answer
39k views

What does COUNT(1) actually count? [duplicate]

Possible Duplicate: What is the difference between select count(*) and select count(any_non_null_column)? I have heard it commonly stated that when counting the number of rows in a query, you ...
1
vote
2answers
504 views

how to get count(*) from table which either has column or not?

I have a post table as below: CREATE TABLE `post` ( `p_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `user_id` smallint(5) unsigned NOT NULL, `cat_id` tinyint(3) unsigned NOT NULL, `...
4
votes
2answers
27k views

Is SELECT COUNT GROUP BY more efficient than counting a result set?

I have a table with a rank column which can be either 1, 2 or 3. I need to count the number of records for each rank, but I also need to display all the data ordered by rank. From my perspective, the ...
18
votes
5answers
25k views

MySQL IS NULL / IS NOT NULL Misbehaving?

Please have look at this table: mysql> desc s_p; +-------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | ...
2
votes
1answer
9k views

I want to count all the rows for todays / yesterdays date based on a DATETIME - mysql+php

I have database "db2" with table "menjava" In table menjava have "id", "author" and "date_submitted" field id - auto_increment author - int(11) date_submitted - datetime I want to count all the ...
3
votes
2answers
319 views

Count() versus keeping a column with a total count [duplicate]

Possible Duplicate: Storing vs calculating aggregate values Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos in ...
2
votes
1answer
429 views

Postgres planer JOIN removal

I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following: ...
2
votes
1answer
317 views

Shortest Query to Combine Count and Unique (Oracle)

I have the following query which works for me: select count(*) from ( select UNIQUE col1, col2, col3 from my_table where cond1 = 'A' and cond2 = 'B' and cond3 = 'C' ) Is it possible ...
12
votes
1answer
8k views

PostgreSQL: COUNT(*) uses a sequential scan, not index

Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?