MYSQL :: Count Identical Rows

Deleting Identical Rows
I have a table with identical row information. I need to delete duplicates. For exaple I have a table my_posts with data:

post_id    post
1          first post
2          second post
2          second post
3          third post

I need it to be:
post_id    post
1          first post
2          second post
3          third post

I cannot find the way to distinguish the rows.

Detect Similiar/identical Rows
I have a table where I register this info:

user
selection_1
selection_2

Lets's asume this records:
| USER | SELECTION_1 | SELECTION_2 |
| A | 1 | 2 |
| B | 2 | 1 |
| C | 1 | 3 |
| D | 1 | 2 |

I want to detect what users have made the same selection (A, B and D: it can be in the same order or not). Is there any mysql function to do this. What is your suggestion for resolving this problem?

Insert Multiple Identical Rows
Is it possible to insert multiple identical rows using a single query without using a loop to build this query. for example:

sqlselected = 0
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN="& websitedsn
oConn.CursorLocation = 3
strSQL = "INSERT INTO testtable (testvar) VALUES ('testinsert') "
oConn.Execute strSQL, sqlselected

I would like to create say 500 of the above rows but not use a loop to build the insert query, and not use a loop to do oRs.addnew or anything like that.. Just a simple 1 line query to add a specific amount of rows. i would eventually like to use a script variable to control how many rows to add.

Get A Count And Some Rows
Can I get a count on the total rows but not return all of them, let's say just get the 10 first rows.

Count Rows
with php and mysql:
How we can count rows in a table?

Count The Rows
I've built quite a few large join statements, which cross-reference 6 or 7 tables each. I'm using LIMIT to allow for pagination, which works fine.
However, I need to find out how many rows were returned in total, and don't fancy throwing in another join to count() the results with some where statements.
Anyway I can quickly find out what would've been returned without the LIMIT?

Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null.

So here is my problem:

I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?

Count() Rows With 0 Matches
I am using the code below to select all the 'centres' in the database that have 'sessions' today.

SELECT c.*, r.region, COUNT(*) AS count
FROM centres c, regions r, sessions st
WHERE c.c_id=st.c_id
AND st.session_date=CURDATE()
AND c.region_id=r.region_id
This works and the results might look like:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3

However, is there a way to get MySQL to also select centres from the database that have no matches in the sessions table? So the results will look like this:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3
centre4 / 0
centre5 / 0
centre6 / 0

Count Same Field In Rows
i need to calculate the number of the same IP address that appear in the database, how can i do that?
example of the database:
IP Address Date and Time
127.0.0.1 5/23/2007
127.0.0.1 5/24/2007
192.168.1.2 5/25/2007
192.168.1.5 5/26/2007
127.0.0.1 5/27/2007

How To Get Count Of Rows Returned While Using Group & Having?
i'm using the having keyword to find certain set of rows and they are working properly.

but what i want is , i want to count the total number of rows returned by this query using mysql.

select name,count(date) from emp group by date having count(date)>3;
this returned returns all the names that have more that 3 entries in the same date. it returned 5 rows and how can i get the count of rows(5) the query returned .

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'.
About half the rows are 'yes' and half are 'no'.
Table is indexed on 'stamped'.
We just need the count on 'yes'.

SELECT count(*) from T where stamped='yes'

or <>'no' or >'n'... is very slow.

Is there a better approach / query to get a fast result?

Count Number Of Rows Returned?
this is my connect and everything code:

<?php // connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die(mysql_error());
$query = "SELECT DISTINCT category FROM $tuttable";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
echo ($row['category']." | ");
}
echo count($row);
?>

Notice "echo count($row);" How do i get this to work properly. It only returns one, because there is only 1 that it is working on. I want to return the count of all the rows that fit that query.

Select Count(*) Showing -1 Rows.
In one of my preliminary tests with mysql, the below statement is showing the following way. What does -1 rows signify in the table with the select count(*) query. Is this (-1 rows in the query result) an error condition to be taken care ?.

bash-2.03# ./mysql -u test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8 to server version: 5.0.18-max

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Code:

Count How Many Matching Rows Exsist
I am new to SQL Querries, can someone help me figure out how to get a result?

I have a column in my db called; emails

some of the email addresses in this column may contain similar domains:

person1@site.com
person2@site.com
person3@someothersite.net

I need the result to count and end up being 2 not 3

Not sure how to make the sql statement:
COUNT (*) FROM database_tabe WHERE email='$email' LIKE '%@%.%'

Not sure how to get this result...

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Count Or Select Only The Populated Rows In A Column?
I'm trying to COUNT the number of populated rows within a column named, 'column_03', but first instead of using COUNT i'm using SELECT to see where the numbers are coming from.
lets say i use the following code...

SELECT column_03
FROM table_name
WHERE table_name.column_01 = 'IN''
AND table.name.column_02 ='GENER"

the result is a total number of 10 records. i can see that 'column_03' has 6 rows that are populated and 4 rows are empty. now what i want to do is select only the 6 rows that are popluated, once i now how to do that i can then use COUNT. Any suggestions?

Limiting Rows Returned Based On Count
say if I have 3000 records in my db, and I want to get rows 200~300 when orderd by a specific field in this table, is there a easy way to do it?

I have something like:

select * from table_name order by ID desc limit ....

but I am not sure if this syntax allows me to do what I am trying to do .

Return A Count Of Number Of Rows Before Desired Row.
Anyone know how? find_in_set doesn't seem to be able to do what I want since I am not searching through an array but am actually running a query.

What I really want is to be able to select all until something = something. I've searched the realm of google but cannot come up with anything useful, and so I am wondering if maybe I don't know the right words to be looking for.

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

Count One Table's Rows From Multi Table Query
here are my tables (condensed)

FEEDS
feed_id
site_id

SITE
site_id
site_name

ARTICLES
article_id
feed_id
link

I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this:

PHP

$gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles
WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection)
or die(mysql_error());

The query does not use JOIN, ON and all that good stuff.

I just need the following variables to run through a loop:

site_id
the number of articles rows per site_id

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

Identical Input Field Value
I have a MySQL Database table, I would like to know how could I have one of my fields in my table to have the same inputs of 3 other fields in the same table with a dash "-" between then when submiting a new entry.

The 3 fields type are VARCHAR.

Identical Input Field Value
I would like to know how could I have one of my fields in my table to have the same inputs of 3 other fields in the same table with a dash "-" between then when submiting a new entry.

Identical Duplicated Functions
Why does MySQL offer almost identical duplicated functions? For instance mysql_insert_id() and LAST_INSERT_ID(), but I believe there are others. Is one a MySQL only function and the other a generic SQL function? Should one be used over another?

Multiple Identical Indexes
I don't know if this belongs here but I wasn't sure where to post.
We have a database the size of 400mb (mysql 4.1.16, innodb). We were having some performance issues (deadlocks thrashing the server) so we started closely examining the database structure. We noticd that there are multiple indexes for the same table on the same column for many of the tables. I'm guessing this can't help in terms of performance and resource overhead. What type of perfromance remifications will multiple, identical indexes have?

Comparing Two Tables Identical Structure
I'm a bit of a newbie, but have worked through a lot of queries and have had great success, until now.

MySQL version 4.0.27 (hosting limitation)

I have two tables where the structures are identical. The only things that are different are that some rows in one are not in the other, and the data in the fields of matching rows are possibly different values. I was hoping there would be a query that would select rows in the first table that have a field that matches a field in the second table, but has different data in any of certain other fields. The first field "name" is the primary key for both tables.

I know that is a mouthful, so more specifically, and simplified...
Table one and two have 'john' in the 'name' field. The other fields, 'level', 'location', 'rank', 'posts' might have different values. The same for other matching names. I want to select all the rows where the names match, but any of the fields do not match.

I've tried a few different queries but throw errors. Mostly on how to compare the fields.

Could someone reply with some examples or direct me to some reading on this subject? I'm learning a lot about MySQL through all this, and willing to learn more.

Merge Tables Identical Column 1
I have three tables in one database with potential identical rows but different columns (exported from excel, thats the reason). Is it possible to do the following:

1) create a new table with the column headers from each of the three tables automatically (a lot of columns, and a lot of tables in total, to do it by hand would take too long)
2) import the columns from each of the tables into the new table, thereby
comparing row1/col1 in table1 with row1/col1 in table2 and, if the are identical, append the data from table 2?

Further explanation (to clarify it, hopefully):
tab1, tab2 and tab3 should have the same values in their respective row1 (lets call them IDs). only the rest of the columns are different. i want to create a merged table, just appending the columns of table2 to the columns of table1 after checking the ID (the identical values in each table).

Merging Tables With Possibly Identical Data
Im looking to merge two tables in my db (or more specifically, insert one's values into the other). This post is pretty helpful for how to set up the merge, but my problem is my two tables will possibly contain identical data - and i dont want duplicate records

Let me explain in more detail - table A contains spots which may be interesting for travellers in a given city. table B has the same data, but from a different source. Both will have the same columns (id, name, subtitle, cat_id, loc_id, address, added, by), but its the data IN the tables that might be identical.

As i said before, i dont want duplicate records, so if the name of the listing is the same as one thats already already in the original table, how can i make it so it isnt inserted?

If it helps, i already have a script which uses fuzzy string matching (written in php) using levenstein and metaphone methods. i dont know if its practical at all, but is there maybe a way we can use that to check each item in table B against the records already in table A before its inserted?

Combining Data From Tables With Identical Schema
I have multiple tables with the same schema but different data. This because I have used census data from the county level to create the individual tables. The tables all have a column named "id" which is a primary key and auto-incremented.

I now wish to combine all of the tables into one table.

I would appreciate if you could explain to me how to combine individual tables into a common table and have a new/unique "id" assigned to each row as it is added?

Similar But Not Identical Input Produces Warning
why inserting

("XX", "test") and then ("XX", "test one") produce a warning?

CREATE TABLE state
(
country_code CHAR(2) NOT NULL,
name VARCHAR(10) NULL,

PRIMARY KEY(country_code, name),

FOREIGN KEY(country_code)
REFERENCES country(code)
ON DELETE CASCADE
ON UPDATE CASCADE
)

Update Online MySQL Database Weekly From Identical Local One
I have two identical MySQL databases. One is offline and is the primary database. The other is online and will have the data from the offline one imported into it. i.e. all the changes and additions will be done in the offline one and then copied over to the online one weekly.

I've got no problem with the SQL for adding the new records and updating those that have changed but don't know how to remove the records that have been deleted from the offline database.

At the moment I am deleting the tables and then adding them again (DROP TABLE IF EXISTS and CREATE TABLE IF NOT EXISTS).

#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows.

I'm using my SQL version: 4.1.19

Any clue on why this isn't working?

INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` )
VALUES (
(
SELECT Project_Code, Grant_Code, FY, SUM( Capital )
FROM Grants_Temp
WHERE Project_Code = 'OSUT'
AND FY = '2006'
AND Claim_Month = '072006'
GROUP BY Grant_Code
ORDER BY Grant_Code
)
)

Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query.
Column count doesn't match value count at row 1

I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query.

The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:

Count(*) As Count And TotalCount Of Count
data in myTable1

(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4

data in myTable4

(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.

I have the following code.

code

(select left(time,2) as hour, count(*) as count
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the code above produces the following result.

result

(day) count
(10) 3
(10) 1
(11) 1
The following would-be code doesn't work correctly, but it will show what I want.

would-be code

(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the following is my target result.

target result

(day) count totalCount
(10) 3 5
(10) 1 5
(11) 1 5

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Difference Between Count(*) And Count(1)
What is the difference between count(*) and count(1)?

Difference Of Count(*) From Count(1)
Is it true that count(1) is more efficient than count(*)? They say that count(*) still goes through all the records without needing to.

How To Fix "Column Count Doesn't Match Value Count At Row 1" ?
I've been trying to install a portal to my installation of Invision Power Board, and I keep coming up with this problem. I post here for two reasons: firstly is that no one is responding over at the portal creator's thread and I need help, second is that this error looks like a mySQL error that could be solved without the need of the creator if possible.

Here is the error returned:

mySQL query error: INSERT INTO `ibf_portal_box` VALUES (1, 'lang.last_topics', '<box><menu>10,20,30,50</menu>
<topics_show>10</topics_show>
<order>last_post</order>
<exforums></exforums>
</box>', 0, 'middle', '*', 0, 4, 'last_topics', '1')

mySQL error: Column count doesn't match value count at row 1
mySQL error code:
Date: Tuesday 21st of December 2004 08:43:10 PM

I am very new to mySQL and don't really understand it. What is wrong with the above that it is trying to insert, and how is it fixed?

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost:

SELECT *,count(*)
FROM cancons c, musics m, discos d, r_discos_cancons rdc
WHERE c.c_id_music = m.m_id
AND rdc.rdc_id_disc = d.d_id
AND d.d_id_music = m.m_id
AND m.m_id = 24
GROUP BY c_id

note:
cancons (ca) = songs (en)
discos (ca) = cd's (en)
music (ca) = musician (en)

don't worry for the WHERE part. i need it because of the foreign keys.
this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think.

I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.

Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A..

Example:

B.part = "abcdefg" matches A.part ="cde"

I cannot put literal in query.. it must be from column data..

It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.