Questions tagged [count]

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

Filter by
Sorted by
Tagged with
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
710 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 ...
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
19k 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+ ...
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 ...
22
votes
3answers
18k views

How to select specific rows if a column exists or all rows if a column doesn't

I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
0
votes
3answers
77 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 ...
11
votes
1answer
7k views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
2
votes
1answer
613 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 ...
-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 ...
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
263 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 ...
8
votes
3answers
24k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
1
vote
1answer
31k 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-...
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 ...
9
votes
4answers
14k 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'...
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 ...
2
votes
1answer
359 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,...
9
votes
3answers
58k views

How to make multiple counts in one query?

I count records with queries like SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%' SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%' SELECT COUNT(col1) FROM table1 WHERE col1 ...
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 '...
5
votes
1answer
322 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 ...
3
votes
2answers
778 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 ...
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 ...
8
votes
1answer
38k 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, `...
6
votes
2answers
923 views

Help with this query

I have a person table. I also have a car table and books table. The car and book tables both have a foreign key back to person. I am trying to count the number of books and cars each person has. I ...
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
318 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
3answers
4k views

Is there a way to find out how many times a stored procedure was invoked since last DB start

I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably ...
2
votes
1answer
426 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: ...
3
votes
2answers
2k views

What is an Effective Way to Count the Number of Pages in Oracle?

We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results. We implement paging by putting the original query inside a SELECT that ...
2
votes
1answer
316 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 ...
3
votes
6answers
37k views

Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so, SELECT MAX('Row') FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS) Error Desc: Incorrect syntax near ')'....
58
votes
4answers
36k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
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?