MYSQL :: Count Or Select Only The Populated Rows In A Column?

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:

Trying To Select Values From Column And Count Each Different One
say my column values are thus:

1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2

my select is like this:

my $sth = $dbh->prepare("SELECT session_id, col2
From $table
");
$sth->execute;

while ( my @fields = $sth->fetchrow_array) {
print qq( $fields[0] );
}

I want to be able to show that there are three individual values (in col1) and that there are 9 values in col 2. I also need to show that (based on the number of values in col2 [relative to col 1]), that the average number is 3

Should I use select using UNIQUE (or some other value), or, should I select as shown but put it in a perl hash - or soemthing else?

Select 5 Rows Per Column Value
ID | NAME | EMAIL | CITY |

How could i select 5 rows for each city value?
In other words, if there are rows containing St. Luis, NY, LA - I want to select 5 users from St L, 5 from NY and 5 from LA.

Select Rows With Common Column
I'm trying to figure out a simple query, and just can't manage it.
I have table like so:

Name-----------Programme-----------Channel
Dave Simpsons Sky One
Dave 24 Sky One
Dave Family Guy BBC2
Nicola CSI Hallmark
Nicola Friends E4

What I want to do is select everything from the table. But, I would like to group them so that the rows returned look like

Row 1:Dave, Simpsons, Sky One, 23, Sky One, Family Guy, BBC2;
Row 2:Nicola, CSI, Hallmark, Friends, E4;

Is this possible, or just plane stupid? I take it I would have to create a temporary table?

Find Users With Similar Tastes / Select Count Of Subquery & Maintain Column Assignmts
The ultimate goal of the query:
List users by similarity in taste to user 1 based on the similarity of their favorite sites.

Setup
table users:
id, etc.

table sites:
id, etc.

table favorites:
user_id, site_id

Users add sites to their favorites list, a row is inserted into the favorites table associating users and sites.

Criteria for "similarity of taste":
%similarity = (number of intersecting favorites) / (number of unique favorites between them)

My attempt:

SELECT COUNT(*) / (SELECT COUNT(*) FROM
(SELECT DISTINCT id FROM favorites WHERE favorites.user_id = u2.id OR favorites.user_id = 1) as unique_favs)
as p_match,
u2.*
FROM favorites as f1
JOIN favorites as f2 ON f2.site_id = f1.site_id AND f2.user_id != 1
JOIN users as u2 ON u2.id = f2.user_id
WHERE f1.user_id = 1
GROUP BY u2.id
ORDER BY p_match DESC
LIMIT 30
The error:
Unknown column 'u2.id' in 'where clause'

Apparently column associations are "forgotten" when doing a derived table subquery.

Any ways I can fix this? Or any preferred methods of achieving the same result?

#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:

Populated Headers And Results
i want to create a links menu for a website. i will display these links from a database. My problem is that i need to have headers for the links... ex:

Link Header .... Example

-link
-link
-link
-link

Link Header .... Example

-link
-link
-link
-link

there is also a function that creates the headers... how would i go about doing this? would i put a query inside a query?

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.

Query Succeeding, But Data Not Being Populated
I'm performing an application integration by directly importing the output data from my program into the said integrated application. Now, I have about 10 queries that run on the "import" page, and for some reason about 1/2 of them actually get commited (I'm not using transactions, and will use "commited" to describe the data actually going into the rows it should). The other half say they were successful and have no syntax errors, but the target database is not updated at all. It's quite a strange thing -- I've never run into anything like this before. It seems to be happening in two completely different environments as well (testing env and production env). I have check user permissions to perform these types of queries, as well as having run the queries directly against the cli/through navicat. No syntax errors, nothing wobbly, just simple update statements.

Upload/download A Populated MySQL Database
I have seen MySQLAdmin for local operations but I now want to put the whole database onto a remote server.
What admin tools should I use?

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

Count Identical Rows
I've got a log table looking like this:Code:

user | action | time
| |
a | click | 2005-05-05
a | login | 2005-04-05
b | logout | 2005-03-02
b | click | 2005-01-25

And now I wanna display the statistics in a neat interface. But I'm having problems... is there a way to select from the above table and get rows like this:Code:
user | click | login | logout
| | |
a | 56 | 23 | 45
b | 45 | 2 | 0
c | 12 | 32 | 32

Perhaps this is more than I could expect from MySQL?

(RIGHT(column,4)*COUNT(*)
I am having an issue with one column in my table that is associated with a dollar amount. (E.g. $6.00 or $26.00)

I am trying to create a query that will take the total count of matching orders and multiply it by the order total.

Example Query:

SELECT *,COUNT(*) AS Count,(RIGHT(ordertotal,4)*COUNT(*)) AS total_rev
FROM `table`
WHERE (table.pkg_number <> '5%')
GROUP BY pkg_number;

The query gives me accurate results for any dollar amounts that are $9.00 and lower. I then change the (RIGHT(ordertotal,4)*COUNT(*)) to read (RIGHT(ordertotal,5)*COUNT(*)) to get results for orders that are $99.00 and lower but the $9.00 and lower results are incorrect.

I am very new at this, can anyone help me with a tip on how to combine them to get accurate results? Or is there a way to create the column in the table to handle dollar amounts more accurately. Currently I am using VARCHAR(25).

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?

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.

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...

One Column Multi Row Count
i cant filter products
a Table:
--------------------------------
[product_no]_________ [filter]
1_____________________2BG RAM
1_____________________INTEL
1_____________________100 GBHD
2_____________________2GB RAM
2_____________________AMD
3_____________________120 GBHD
4_____________________4GB RAM
4______________________AMD

1. problem
i need like this:
[filter]_____[product count]
2BG RAM_________(2)
4BG RAM_________(1)
100 GBHD________(1)
AMD_____________(2)
INTEL___________(1)

2. problem
and how many product with 2GB RAM:
2GB RAM and 100 GBHD
2GB RAM and AMD
2GB RAM and INTEL

i need like this: (in web navigation Computer>Laptop>2GB RAM)
[multi_filter]_____________[count]
2GB RAM + 100 GBHD___________(1)
2GB RAM + AMD________________(1)
2GB RAM + INTEL______________(0)
.............

for web:
Laptop
_____2GB RAM (2)
___________100 GBHD (1)
____________AMD (1)

Getting A Total Of A COUNT Column
I am building a simple poll system and part of this is a query to pull the poll results from a MySQL table.

The system contains 3 tables.
pollquestions - Contains just an id and question string.

polloptions - stores options for each question. contains id, questionid and a string for the option

pollanswers - contains the 'votes' cast in each poll. Contains and id, question id and option id.

To get the results data for a poll I'm using the following simple query which seems to work OK. (in this case I am grabbing the results for poll '2')

CODESELECT
COUNT(*) as votes,
polloptions.id,
polloptions.option
FROM pollanswers
LEFT JOIN polloptions ON polloptions.id=pollanswers.answerid
WHERE pollanswers.questionid=2
GROUP BY pollanswers.answerid

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());

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.

SELECT COUNT(*) Or SELECT .. LIMIT 1?
If the purpose of a query is to see if atleast 1 record exists which would be faster?

SELECT COUNT(*) or SELECT .. LIMIT 1?

Both would use a WHERE clause.

Set Column Value In X Rows
I have a database of URLs that is changing constantly. The fields are id (autoincrement), URL, valid, time, new. When a URL is added, valid is set to 0 and new is set to 1. I have an admin panel, where the admin can choose to activate any amount of URLs that he wishes. So what would the code be for that? I'm a mediocre mySQL guy, so this is really causing me some problems. If the admin enters 10 into the form, then I need to take 10 URLs where valid=0 and new=1 and set them to valid=1 and new=0.

Two Rows From Same Column
I have a forum table that contains the last user that posted on the forum and the posts original user. I want to get both of those users names and IDs from the user table in one SQL query

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...

SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using

SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

Update All Rows In A Column
I'm a complete newbie but I'm trying to do something that I would think would be easy. In my table I have a column LICLEVEL with values of Club, Expert and Elite. I need to tack on "USA Cycling - " in front of that value, so that if the current data is "Elite" after the update it will say "USA Cycling - Elite". With 800 records to update, I'm hoping there's a fairly easy way to achieve this with SQL (or PHP).

Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends.

Will like to know:

Assuming I have a table with 8 columns.

When I am querying these records with specific WHERE conditions,
will SELECT * be faster, or SELECT (3-5 x columns) be faster?

Wanting Distinct Rows (by Only One Column), Not Sure How To Do It
I have a table `logins` with id, name, ip, mac, date

ip and mac are both tracking information for name and I'm trying to write a query that, when given a name, selects all names that person has logged in with by cross-checking their ip and mac with every record we have for that name

I have this query so far but it gives duplicates in the name field. What I'm trying to do is get it to give me each name only once and the most recent date associated with that name.

SELECT *
FROM `login_history`
WHERE `ip`
IN (

SELECT `ip`
FROM `login_history`
WHERE `name` = 'Name'
)
OR `mac`
IN (

SELECT `mac`
FROM `login_history`
WHERE `name` = 'Name'
)

Replace Values In One Column All Rows
I need to replace all values in one column with the values from another column - example:

I want to replace all the values in the Username Column with all the values in the EmailAddress column - all rows to be effected.

Getting Total For All The Rows From A Single Column
How can I get the total of all the rows from a single column.

Find Rows With Same Info In A Column
How can I query so that I find all the rows where the information in the same columns is the same? So that I can get the information from both rows?

Concatenate Multiple Rows On Same Column
I have the following table structure holding product long descriptions:

Part No(pn)Sequence (seq)Long Description (long_desc)
--------------- --------------- ----------------------------------------
HL145010This is part of a long description and
HL145020it overlaps onto several lines. I'm
HL145030having difficulty writing the SQL that
HL145040is needed to make this work...

I need to write SQL that will give me the following output:

Part NoLong Description
--------------- --------------------------------------------------------
HL1450This is part of a long description and it overlaps onto
several lines. I'm having difficulty writing the SQL that is needed
to make this work...

(Essentially it a GROUP BY on the part number with an ORDER by on the
sequence field). However, I know how to concatenate multiple columns on
the same row (using the CONCAT or CONCAT_WS functions) but I'm at a loss
over how to concatenate the same column over multiple _rows_

I am aware of the GROUP_CONCAT function, but we are currently running
MySQL 4.0.13 (this functionality is only available on a vers > 4.1)

How To Select Count(*) And Something Else?
How do you select a count and another field without having to do two queries?

here's the query I'm using:-

SELECT
count(*) as totals, user

FROM user_posts
WHERE post='$md5'

SELECT COUNT
I'm counting rows from a mysql database able and trying to display the count. I have the following.

$count = "SELECT COUNT(*) AS newcount FROM stores";
$cr = mysql_query($count);
if (!$cr)
die(mysql_error());
if($cr){
//Make site ID now ...
$var1 = $newcount;
$var2 = P00;
$var3 = $var2.$var1;
}

The problem I have is thet $newcount seems to be empty. I have used count and not put "AS newcount" in the query but that made no difference. I have tried to echo the $newcount but again it is empty. Now, in PHP is there something else I must do to return the value of $newcount that I have missed?