MYSQL :: Quick COUNT()

Quick Fix 4 MySQL Pro
I will have a few instances like this and since I'm new to MySQL, perhaps a good example will help down the road. All I want to do is display a list of the first and last name of the names in my DB so they appear like so:

John Smith
Suzie Johnson
Etc...

first and last are two separate fields in the DB so I just need to join them together. The code I have now gives me this error:
PHP Notice: Undefined index: first in e:etc...

<?php
// Request the names in db
$result = mysql_query("SELECT artist.first, artist.last FROM artist");
if (!$result) {
echo("No artists posted at this time");
exit();

code...

Quick Join
I have a quick SQL question:
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys. Now I want the querry result to look like this: Match ID | Winner Name | Loser Name.

I did a join query:
Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id
How do I get the loser name?

Quick PhpMyAdmin
I believe I got phpMyAdmin setup to work right, I used HTTP authentication, and I get a log in screen when I goto the url. How do I set up an account for that?

GRANT ALL PRIVILEGES ON acct1_db.* TO 'Brian'@localhost IDENTIFIED BY 'password';

Is that right? I wish to host websites off this computer and have multiple users. Do I need to log in to the controluser account to do it? Or am I way off?

Quick Query
this one shoud be easy, but I just can't think of the best way (other than writing a php script to do it). I have a CMS and need to change one word in the description field for 64 rows. For example, where we now have 64 entries with "Bilbo" in the middle of the data, we need to change it to say "Frodo" instead. (Actually, I need to replace a misspelled javascript toLowerCase() call...darned non-programmers! They used toLowercase() - note the lower case C)

Quick Queries
Is it possible to use a regular expression or similar in a mySQL query, to make 'fuzzy' comparisons, or is LIKE the best we can hope for?

QUICK AND EASY ONE
im fairly new to php/mysql so please bear with me
I am trying to upload a txt.file created from an excel spreadsheet using the following query:

LOAD DATA INFILE 'path/file.txt' INTO TABLE my_table;

im getting a syntax error!
what could be wrong? are there any other attributes i should be adding to this query? i also tried uploading a csv version of the same file and get the same error.

Quick Tip: Switching Field On/off
Ever wanted to change a database field (typically TINYINT(1)) from 1 to 0 or from 0 to 1 depending on the current value?

No need for two queries. I just thought of this 'clever' way to take care of it. Never saw that done this way before, and should have thought earlier about this.

UPDATE articles
SET published = ABS(published - 1)
WHERE id = x

How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

Quick Ones To Speed Up Access
------=_NextPart_000_0032_01C35FC2.62884DE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I am querying from a table containing more than 40,000 records. Earlier =
when the records were 10,000 it was taking 9 sec and now after 1 year =
and 40,000 records its taking 30 sec. Code is the same.=20

I am pretty sure that it has something to do with database only. No body =
can think of spending 30 secs for retrieving values from tables unless =
it's very huge in the sense of millions of records. =20

I don know whether I have to modify my database or do some sort of =
restructuring or reindexing so as to make it fast enough.

Is there some methods or optmization which can be applied to this =
database which hasn't been touched since design to enhance the spped.=20

If there are some quick ones but valued alottttttt please let me know.

Dump (quick) A Table Into Another One
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

I'm looking for a faster way. Does anybody know it? Thank you very
much.

PS: I was told about 'mysqldump', but I do not know if it's faster
than mine.

Quick Date Subtraction Question
When I do the following, I get the number of days in difference between two dates however for the dates that have expired, I get the number of days back negated, ie -3 for example?

What I actually want is that if there has been an expiration, I want the result for a given row to be 0. Anyone got an idea how to?

// timestamp is datetime creation of record
// expires is date in future, ie 7, 14, 21 or 28 days in that increments
mysql> select day( expired ) - day( now() ) as days from polls;
Here is an example of the data returned,

+------+
| days |
+------+
| 7 |
| 7 |
| -3 |
| 7 |
| 7 |
| 21 |
| 7 |
| 7 |
| 14 |
| 21 |
| 7 |
| 21 |
| 7 |
| -3 |
| 21 |
| -3 |
| 7 |
| 7 |
| 7 |
| 14 |
| -3 |
+------+

Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes.

I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?

Quick Question - Id Mediumint(8) - Does The (8) Matter?
just a quick question really..

Just setting up 1 of my tables and just wondering if it matters if i set my id to mediumint(8) but i only ever get to 6, will this effect anything in anyway?

Same goes for - address varchar(50) and say that no address ever reachs 50?

Quick & Easy MySQL Sync?
Does anyone know of any quick and easy way to sync a local development mysql server to a remote server and back. I'd like to be able to optionally sync either direction?

Quick Mysql Auto_increment Question
i noticed that after adding some test data into my table the autoincrement is working fine. but after i delete them, even though the table is empty again the new data i put in will have primary key thats in auto_increment as if the old data was there.

for example i added in 2 rows. now i have an id 1 and id 2. i delete them and the table has no rows. but now i add another two rows after wards and they get assigned id 3 and id 4. is there anyway for me to make the counting start over??? thanks for any replies or thoughts on this.

Quick Question About Reseting Demo Database?
I would like to set up a cron job to reset my demo database on a regular basis. Is there a way to export an sql file via phpmyadmin, and run it as a whole via the PHP mysql commands? The only way I can see to do it, I have to split up the exported sql from phpmyadmin into individual queries. I'd like to just process the entire .sql file at once, as it would make maintenence much easier.

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

#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

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?

Quick "WHERE"
Part of a WHERE criteria I'm trying to construct needs to return only those records where a date field in my adkey table contains a date older than a user-specified date (submitted as variable {sRemoveDate} from a web-page form). The following works:
CODEWHERE
(adkey.D_RemoveDate < '{sRemoveDate}' Or
adkey.D_RemoveDate is null)

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.

Count()?
Im trying to count the # of records with the same field (Name) is this the way to do it?

PHP

$Count = mysql_query(" SELECT count(ID), Name FROM REVIEW WHERE Name = '".$Name."'") or die(mysql_error());

Then to display the count all i need to do ia

PHP

echo $Count;

Count()
In my database I have Racing Events and Racing Results.

In the result database each racer that entered the race is giving a place.

I am trying to pull the each total number of races racer with id 1 was in.

Say the Count was 20. I also want to get the total number of people that raced in the same events as him. Say each race had 10 people that count would be 200.

In additional to that I want to count the number of time the racer with id 1 won money.

So say that field was called winnings - Winnings > 0

Add One To Count
im sure this is very simple and that its a stupid moment i am having.

I have a column called db_count and im looking to increment it by one... I have tried this and it wont work, but i cant tell if its the other code i have that wont or this,

is this the way to do it?

UPDATE tbl_makes
SET db_count= db_count +1
WHERE make_name=཈'

Using LIKE Within COUNT?
I am having problems with this query. I have it looking at a table of products sold data - specifically shoes. With shoes there is always the problem of fishing out the data for different sizes.

With the query below I am not sure whether I am able to use LIKE within the COUNT function.

I basically want the query to return a table showing the headings Product which contains all the products (grouped) and COUNTs the number of times each product size comes up.

Here is an example:

+-------------+-----------+-----------+------+-----+-------+
| Product | UK Kids 12 | UK Kids 13 | UK 1 | UK 2 | UK ... |
+-------------+-----------+-----------+------+-----+-------+
| Product 1 | 34 | 52 | 39 | 22 | .... |
+-------------+-----------+-----------+------+-----+-------+

And so on...

Here is the query I have currently however at the moment all it seems to do is return the same number (which happens to be the total of all the sizes - i.e. the total for that shoe model) for each product accross the whole row.

SELECT
SUBSTRING_INDEX(Product,',',1) AS 'Product',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%Kids 12%') AS 'UK Kids 12',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%Kids 13%') AS 'UK Kids 13',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%1%(') AS 'UK 1',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%2%(') AS 'UK 2',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%3%(') AS 'UK 3',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%4%(') AS 'UK 4',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%5%(') AS 'UK 5',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%6%(') AS 'UK 6',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%7%(') AS 'UK 7',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%8%(') AS 'UK 8',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%9%(') AS 'UK 9',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%10%(') AS 'UK 10',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%11%(') AS 'UK 11'
FROM items_ordered,orders WHERE (Product LIKE 'Heelys%Shoes%') AND

(items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <= &#55614;&#57158;-8-31') GROUP BY Product;

Help With Count(*)
I have a table I'm using for logging purposes with a schema like:

create table results (
user varchar(255)
....
);

Where user is not a unique field and I want to find out how many unique
users there are in the table.

I want to do something like:

select count(count(*)) from results group by user;

But that doesn't work.

Count () Sql How To Use It?
SELECT COUNT(*) FROM Persons WHERE _____________

"________" = I don't know what to enter

I want it count the entries of visitors
whose First Name, Last Name & Email are same

Like this

------------------------------------
First Name Last Name queries
------------------------------------
xyz abc 2
-------------------------------------

First_name
Last_name & email are table columns

Count??
$num = mysql_query("SELECT COUNT(*) FROM `shoutbox`");
echo $num;

it returns "Resource id #28" my table does not have 28 fields but the id goes up to 28 i do not know what is wrong and i am trying to get the number of fields in my table.

Trying To Do A Count
My table is used to keep history of books checked in and out. I need to compile statistics to show number of check in's and check outs each day.

I have been able to generate a report for the check in and checkouts seperately but would like to have them displayed on one report instead of two.

There is one column (status_cd) in the table that records the book status as a 'out' or 'crt'(in the shelving cart).

I merley want to count the number of out's and crt's per day and display them in the same output display. Code:

Count
I have a table full of blog posts. I dont want to create a table just to log hits so i thought i would add a column to that table called 'views'.

I want to create a SQL UPDATE to count++ this, can it be done just int eh sql?

COUNT()
I need to write a SELECT statement that will display the most popular
categories.

This means I need a 'category count' for each of the messages in the
messages table, and I don't know how to do this.

Here is the structure of the 2 tables:

messages table
id, title, message, category, thread, status, date_posted

categories table
id, title, sub_cat

I've tried the following:
select categories.id, categories.title, count(messages.category) as
count from messages, categories group by categories.title;

but 'count' only returns the total count of messages with a category
entry, and I need the number of times each category id is entered in
the messages table.

Any help is greatly appreciated. Also, does anyone have any online
references for learning more complex SQL statements?

Count(*)
I have a table I'm using for logging purposes with a schema like:

create table results (
user varchar(255)
....
);

Where user is not a unique field and I want to find out how many unique
users there are in the table. I want to do something like:

select count(count(*)) from results group by user;

But that doesn't work..

Count(*) And Sum(1)
I am having this interesting SQL problem with mysql Ver 12.22 Distrib
4.0.16, for apple-darwin7.0 (powerpc). For some reason count(*) does
give the right result, when I replace it with sum(1) it works.

mysql> select term, year, count(*) from course where year = 2001 group
by term, year;

+--------+------+--------------------+
| term | year | count(*) |
+--------+------+--------------------+
| Fall | 2001 | 360287970189639680 |
| Spring | 2001 | 360287970189639680 |
+--------+------+--------------------+
2 rows in set (0.00 sec)

mysql> select term, year, sum(1) from course where year = 2001 group
by term, year;
+--------+------+--------+
| term | year | sum(1) |
+--------+------+--------+
| Fall | 2001 | 5 |
| Spring | 2001 | 5 |
+--------+------+--------+
2 rows in set (0.00 sec)

Here is the schema.........

Using COUNT
I have a problem on using the COUNT Function

TableA
+------+-------+----------+
| ID | Status| BatchID |
+------+-------+----------+
| 1 | Pass | 1 |
| 2 | Fail | 1 |
| 3 | Pass | 1 |
| 4 | Pass | 2 |
| 5 | Fail | 3 |
+------+-------+----------+

QUERY Result
+--------+-------------+------------+
|BatchID | Status_Pass | NoOfRecord |
+--------+-------------+------------+
| 1 | 2 | 1 |
| 2 | 1 | 1 |
| 3 | 0 | 1 |
+--------+-------------+------------+

My Query
SELECT A.BatchID, TA.Status_Pass, COUNT(*) AS NoOfRecord
FROM TableA AS A LEFT JOIN
(SELECT BatchID, COUNT(*) AS Status_Pass FROM TableA WHERE Status = 'Pass' GROUP BY BatchID) AS TA
ON (A.BatchID = TA.BatchID)
GROUP BY A.BatchID

Help With Count
I have 2 tables.

TableA
---------
int tableAPrimaryKey

TableB
---------
int tableBPrimaryKey
int tableAForeignKey

For each row in Table A, I am trying to get a count from Table B

select a.tableAPrimaryKey, count(*) as myCount
from TableA a, TableB b
where a.tableAPrimaryKey = b.tableAForeignKey
group by a.tableAPrimaryKey

This query returns the correct results, but only if the count is >= 1. I want a count for every row in TableA, even if the count is zero (or null...which I would count as zero)

The following query accounts for nulls, but the count ends up being 'one' instead of 'zero'.

select a.tableAPrimaryKey, count(*) as myCount
from TableA a
left outer join TableB b on a.tableAPrimaryKey = b.tableAForeignKey
group by a.tableAPrimaryKey.

Getting A 0 Count
I have a query, which essentially is this.

select count(visible) as cVis, Visible
from products

Visible can have a value of 0 or 1.. and I'm trying to figure out how to always get a count on both (Invisible 0 and Visible 1).

Right now if there's no count for one of them, it doesn't return it.

Count
I have a column that has about 50 possible values that can be put into it. I am trying to generate a query that can tell me how many times each of these values has been used.
Right now it's slow and painful going through and running 50 scripts like this:

SELECT count('col3') FROM tblStuff WHERE col3='widget1'
What would be nice would be to run a script that returned something like:

widget1 = 105
widget2 = 309
widget3 = 92
...
widget50 = 110

Count()
Is it possible to use count() twice in a query if I'm joining tables to count records in each table?

COUNT Is 0
I have the following query:

SELECT u.id,u.username, COUNT(p.id) FROM '.$db->prefix.'users AS u, '.$db->prefix.'posts AS p WHERE u.referred_by = '.$id.' AND p.poster_id = u.id GROUP BY u.id

The gist of the query is to list all members that were referred by a specific member and count the number of posts those referred members have made (this is for a forum). The problem is, this query will not show results if the referred member has made 0 posts.

COUNT(*)
Seems my Host uses version 4.0.24 and it is fussy about using COUNT(*) if I am reading the error message correctly. Is there another way to make this work for this version?

LEFT OUTER
JOIN (
select Players_Player_ID
, count(*) as player_goals
from goals
group
by Players_Player_ID
) as G

Count(*)
I'd like to get a row count for a large table, but I've recently been considering if count(*) is too resource intensive? Is there a better way? A factor here might be that an exact count is not necessary, so if it's off by a few, I'm not concerned. Now, you might say "not an exact count?!?!?" You see, I was looking at SHOW TABLE STATUS, but I don't want to have to cycle through the rows to get to the table I'm interested in...

Count(*)
select count(*) from syslog.SystemEvents where message like
'%postfix/smtp[%' and (message like '%@carcass.us>%' or message like
'%@deadghost.com>%' or message like '%@phatho.com>%');
Above query returns error. Have no idea how to count something like this and
the manual is not helping. I'm thinking select it into a temp table and then
count? I have had no luck with temp tables either. They just don't seem to
work for me.

Quick COUNT()

The MySQL documentation says: COUNT(DISTINCT expr,[expr...]) Returns a count of the number of different non-NULL values. How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.

The MySQL documentation says:

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.
How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.