MYSQL :: Counting Fields In A Table

Counting Fields
I need to retrieve the number of occurences of each distinct field in a specific column.
This is what I am doing right now:

Code:

SELECT DISTINCT referrer FROM counter

and for each record that that query returns:

Code:

SELECT COUNT(*) AS count FROM counter WHERE referrer = '".mysql_real_escape_string( $row[ 'referrer' ] )."'
(using PHP)

is there a way to make a single SQL query that will count the occurences of each distinct field of a specified column?

Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning.

current query looks like this:

SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id;

So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

Counting Row On Joined Table
I'm looking for a querythat does something like this in just one query, currently the DB selects all the page changes and outputs them into a table, on each iteration of the loop a separate query is called that checks how many comments there are for this particular query, there's not about 150 changes so the query count is starting to get a bit big.

I tried this:

Counting Rows From Other Table
SELECT one.this, COUNT(two.this) FROM one, two WHERE (two.this = one.this)The problem with this query is that it won't return rows from table "one" if they don't match with at least one element on table "two". How to proceed then with just one query?

Counting Sub-table Records
I need to write a SPROC that will be pulling information from several tables, altering formats, and inserting into new tables. The query is several hundred lines long. Most of the tables have a related index field.

What I need to do is use a few LEFT JOINs during the SELECT for one of the tables. I need to know, for this table, how many records from some of the other tables have matching Id numbers. I am having trouble figuring out the COUNT statement. Code:

Counting Rows In A Table
Anyone know how to count rows in a table depending on 2 columns, and
different values within the second column.

Basically i am trying to obtain the resultset example below from the table
example below

TABLE example:

Date | Status
-------------------------
2003/07/01 | 1
2003/07/04 | 1
2003/07/06 | 1
2003/07/24 | 2
2003/07/24 | 2
2003/07/24 | 1

RESULTSET required:

Date | Status = 1 | Status = 2
-----------------------------------------------
2003/07/01 | 1 |
2003/07/04 | 1 |
2003/07/06 | 1 |
2003/07/24 | 1 | 2

Counting Total No. Of Columns In A Table
I am having problem in connection mysql with java. Curretly I am using JConnector.

I want to know the query for total no. of columns in my table which will help me in feeding up the data. I have tried following queries
-------------------------------------------------------------------
mysql> select count(*) from ALL_TAB_COLUMN where TABLENAME=REFERENCE;
ERROR 1146 (42S02): Table 'customer.all_tab_column' doesn't exist
mysql> select count(*) from ALL_TAB_COLUMN where TABLENAME=REFERENCE;
ERROR 1146 (42S02): Table 'customer.all_tab_column' doesn't exist
mysql> SELECT COUNT(*) FROM R_TAB_COLUMNS WHERE TABLENAME=REFERENCE;
ERROR 1146 (42S02): Table 'customer.r_tab_columns' doesn't exist
mysql> SELECT COUNT(*) FROM user_TAB_COLUMNS WHERE TABLENAME=REFERENCE;
ERROR 1146 (42S02): Table 'customer.user_tab_columns' doesn't exist
mysql> SELECT COUNT(*) FROM user_TAB_COLUMNS WHERE TABLENAME=REFERENCE;
ERROR 1146 (42S02): Table 'customer.user_tab_columns' doesn't exist
------------------------------------------------------------------------

Averaging And Counting When Joining A Table To Itself.
I have a table which allows users to rate a product (details at the bottom). Each user can make two types of ratings on a product - 'Looks' and 'Functions'.
A user can give a rating for none, one or both of the rating types.

What I would like to do is create a query that will tell me the average rating for Looks and Functions as well as the number of users that the average was taken from.

At the moment I run two queries:

SELECT AVG(rating), count(*) FROM ratings WHERE ratingtype='Looks' AND productid=n
SELECT AVG(rating), count(*) FROM ratings WHERE ratingtype='Functions' AND productid=n

Ideally I would like to do this in one query, to reduce the number of queries that are being run. (The page that shows all of the products currently runs hundreds of queries - one to select the details of each product and then for each product the above two statements are run).

Is this possible? I have tried a number of different queries, none of which work. The closest I got is (note: typed out, so may contain typos): Code:

Counting Rows In One Table That Match Something In Another
I have 2 tables, events and photos

The photos table contains references to uploaded photos as well as the event id that those files refer to.

I am writing some code to output a list of events and want to say whether or not there are any photos associated with that event.

I have a query like this
CODESELECT e.id,e.eventName,e.eventLocation,e.publish FROM shareEvents e,sharePhotos p ORDER BY id DESC

Counting Records In Related Table
I'm trying to construct a single query that returns all records from one table plus a count of all corresponding records in another table. I have a table of members (members) and a separate table (traffic) that tracks what each member has downloaded from the website I've created. Here is the current SELECT statement I'm using:

SELECT * , COUNT(*) AS traffic_count
FROM `members` , `traffic`
WHERE members.id = traffic.member_id
GROUP BY members.id
ORDER BY traffic_count

This almost works for me. The problem is that it only returns results for the members who have corresponding records in the 'traffic' table. This is a great start but I'm trying to return all records from the 'members' table including a corresponding 'traffic_count' variable for each member. This means that for each member that has no entries in the 'traffic' table I'd like the 'traffic_count' variable to be 0.

Counting Occurrences Of Values In Table Column.
I have two tables;

Table 1;

Agent_ID UserID First_Name Last_Name

1ShadShad Mortazavi
2Harry Harry Potter
Table 2;

Recording_IDAgent_ID Status GSM_File etc
11 000001.gsm
21 0 00002.gsm
32 100003.gsm
41 200004.gsm
51 100005.gsm
61 200006.gsm
72 100007.gsm

Status 0 = Red, 1 = Yellow and 2 = Green

I would like a query that returns the number of occurrences of Red, Yellow, Green against each agent

so the data returned would look something like this;

NameREDYellow Green
Shad Mortazavi 2 0 2
Harry Potter 0 2 0

I'm using MySQL version 3.23.58

I can do this programmatically in Perl with several query's; but this is CPU intensive. If I could get this in one query I it would save time.

Counting Levels On Adjacency Based Table
Does anyone know a single query that could successfully count the number of levels in a table based on the adjacency model?

By "number of levels", I mean the number of LEFT OUTER JOIN that needs to be used in a query such like this one :

SELECT
level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name,
level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name,
level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name,
level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name
FROM ecom_categories AS level0
LEFT OUTER JOIN ecom_categories AS level1 ON level1.cat_parent_ID = level0.cat_ID
LEFT OUTER JOIN ecom_categories AS level2 ON level2.cat_parent_ID = level1.cat_ID
LEFT OUTER JOIN ecom_categories AS level3 ON level3.cat_parent_ID = level2.cat_ID
WHERE level0.cat_parent_ID IS NULL
ORDER BY level0_name, level1_name, level2_name, level3_name

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

Getting All Fields From A Table And ONLY ONE From Another One
maybe it's time to get some rest, today...

SELECT * , User_Avatar
FROM restaurantsrate, users
WHERE Card_ID =222443
AND Ratings_Deleted =0
AND Ratings_UserID = User_ID
LIMIT 0 , 30

I'm trying to get all fields from table restaurantsrate and only User_Avatar from table users, but I'm getting ALL fields from both tables? anyone can help?

How Many Fields Can A Table Have?
I'm presuming you can have as many fields as you'd like, but I'm just curious as to what the maximum recommended is. Is it ever better to split the fields over 2 tables instead of one? even though most the time if you're grabbing data from table1, you'll also be getting data from table2. The question popped into my head as I was making a "Orders" table for an online store. It has 39 fields so far and I'll probably need to add a few more (depending on what other information the client needs to know about their customer orders).

Table Fields
Is it ok to have a big table with 27 fields or should I try and split it down into smaller tables. It kind of makes sense all being in one table to me, but if that is bad design then I would break it down.

Sum 2 Fields From 2 Table
I have facing a problem to sum 2 fields from 2 table.

SELECT Table1.Stud_Number, Sum(table1.School_fee), Sum(table2.libary)
FROM Table1, Table2 WHERE Table1.Stud_Number = Table2.Stud_Number
GROUP BY Stud_Number

Db Table Fields
Is there an industry standard or best practice some of you more experienced developers could pass along concerning the number of characters I should define in my tables for a person's first name, last name, username, phone, email... and any other fields you think worth mentioning?

My Password is encrypted with sha1(), so that field is 40 characters long.

I'm looking to keep the tables as efficient as possible, so I'd like to follow some 'norms' if there are any.

Fields In Table
How do I fix this error? My tables have data.

More Than 50 Fields In A Table
I had made a table which contains more than 50 fields..
I am afraid this will hamper later ..
What i want to know ?
1>how many fields should be in a table for optimization ?
2>what should taken into consideration for future hazards in case there are more than
50 fields in table ?

SUM Of Two Foreign Table Fields
I've a project that has many timesheets (time) and many expenses (money)

I'm trying to work out the cost of the project base on a set amount per hour for timesheets plus expenses.

A project may not always have expenses.

A project has zero or many timesheets
A project has zero or many expenses

I need an SQL statement to return total time for a project and total expenses so i can do my calculations then in php. I'd prefer do this in one SQL statement.

What i have so far:

SELECT project.name, SUM(timesheet.time) AS time, SUM(expenses.amount) AS expense
FROM project, timesheet, expenses
WHERE project.id = timesheet.project_id
AND project.id = expenses.project_id
GROUP by project.id

==TABLES==

PROJECT
id PK
name

TIMESHEET
id PK
project_id FK
time

EXPENSES
id PK
project_id FK
amount
This will work fine for all projects that have both at least one expense and one timesheet. But if they are missing one then it returns no row for that project and therefore i can't display the cost.

There are other pieces of data but these are the only important ones. I can do it in two different SQL statements but i really think there has to be a way to do it in one.

Merging Two Fields In A Table!
I have used a join to bring two tables together, which works fine, but what I now have is 2 fields with dates in which I would like to order by, and at no point are these two dates overlapping, is there a way I can merge the two together, or order by both so as they overlap. My current SQL is:

SELECT * FROM tblblog
LEFT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

UNION

SELECT * FROM tblblog
RIGHT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

ORDER BY blog_date DESC, date_watched DESC
Thankyou for any advice you may be able to give
Dan Duke

How Many Fields Of A Table Are Recommended?
May be a stupid question, but just want to know, how many fields/columns are recommended ? I know, it depends how many someone needs, but incase if a person needs alot of fields, then for example, is it okay to make 200 fields of a table of mysql database? or its not good to add so many fields ?

Two Similar Fields One Table
I am trying to figure how to make a single select query in one table between
two fields to see if they are similiar. I need the second one to have a
wildcard. I can easily do this with no wildcard to see if they are the same
but I can't get anything to work when it should be LIKE.

Example:

Table: test
Field1 = "testing 123"
Field2="testing"

I need something that selects that row because the word "testing" is in
both.

Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that
is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't.

ID int(10),
std varchr(50),
atd varchr(50),
dtd varchr(50),
ctd varchr(50)

I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also.
If I change to another record it may all work or not. Any Ideas at all?

Adding Fields To Table
Once a table has been created, can additional fields be added? What would be the syntax?

Update Fields In Table
I am trying to update multiple records,rows,fields in a table. Below is what I am placing in the sql field in phpmyadmin. It works with one update line but not more. What am I missing here?

UPDATE Products SET TitleTag='U.S. Air Force Retired Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=28;

UPDATE Products SET TitleTag='Vietnam Veterans of America Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=29;

Table Fields Do Not Show Up
When creating a connection to my mysql database using dreamweaver mx, or various other programs, i can see the table names once the connection is made, but the fields do not show up with the exception of a couple of tables. I've looked over those tables several times and cannot find any differences that would cause this problem. The only program this does not seem to affect is phpmaker.

SHOW FIELDS FROM Table?
My code is shown below, I can select * from employees but I cannot get
any data back from SHOW FIELDS, I do not receive an error, in fact,
something is returned but I'm not sure what. I can execute the
command:

SHOW FIELDS FROM test.employees

in the Control Center just fine, even cmd.ExecuteReader will enter
reader.Read() once, something is returned but what does it look like?

Maximum Fields In A Table
I have a website which runs pretty slow... I have turned on the SLOW-QUERY-LOG, and there's one table that is in the log over and over again...
It's the table which stores 99% of the user information.
This table has 61 fields, and alot of these are varChar and their set at 100 or so....
Anyways, I was curious what is the maximum amount of fields you should have in table, and in my case should I split the data between several tables and just use a complex Join statement?

93 Fields In A Single Table
I'm putting a database together for a client, he has a questionaire that comprises of 93 questions that range between yes or no's to if no why not (text and varchar fields)
I think his questionaire is ridiculous but he is the client at the end of the day.
I have been trying to decide how to put this monstrosity together and it looks as though a table with 90+ fields is going to be the result because there are no field repeats. I could design a generic "yes and no" table then populate it with the "yes no" questions linking it with a relationship but then the coding to ensure order is going to be a lot of extra work...Dwane

How Can I Count Non-empty Fields Of A Table
there's a form with some fields, after submitting, all non-empty inputs are in mysql table's field. how can i count how many are non-empty fields in a row of mysql table?

Prefix Table Name On Result Fields
this has to be a simple one, but it has me stumped.

I am doing a join across 10 tables. Some tables have conflicting field names (i.e. they are the same). When i access the resultset by fieldname, obviously at times I get a conflict. I cannot access the fields by the index position.

my join is in this form:

Select * FROM table1 LEFT JOIN table2 ON table1.id = table2.id2;

Now, how can I get the table names to prefix the returned columns?

I am using the creole framework for anyone who may be interesed, but have access to the RAW SQL.

Simple: Adding New Fields To A Table
i'm VERY VERY new to mysq! i have an existing table with 4 simple fields: id, lastName, firstName, and email.

i'm trying to create a registration page, and need to add a few more fields to the table: StreetAddress, City, State, and ZipCode.. i just cant remember what the sql statement is, or where to find it, and i'm not sure "what" its called - and that makes it hard to google.

i'll add the new feilds using the command line, or more likely just type the code directly into phpMyAdmin. the table name is tblCustomers

hopefully i've explained myself well enough. i know adding new fields to a table has to be about as basic as it can get.. but i cant remember how to do it!

edit:
so i did find the code i used to create the table

create table tblCustomers(
AdminID int unsigned not null auto_increment primary key,
LastName varchar(50),
FirstName varchar(50),
Email varchar(80),
AdminPW varChar(20)
);

insert into tblCustomers values
(NULL,"Smith","Bob","bob@example.com","newpass"),
(NULL,"Jones","Bill","bill@example.com","newpass"),
(NULL,"Doe","John","john@example.com","newpass"),
(NULL,"Rules","Ann","ann@example.com","newpass");

Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication?

for example..:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(40) NOT NULL default '',
`Password` VARCHAR(40) NOT NULL default '',
`Lastlogin` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like:
lastip
createdon
lastlogin
active ('yes', 'no', 'banned') -

How Can I Compare Two Table Fields In One Query?
This is my problem:

"SELECT * FROM a,b WHERE a.field LIKE b.field " --> this works

"SELECT * FROM a,b WHERE a.field LIKE %b.field% " --> this works not

My Question:

Is it possible to compare two filds with the substitute symbol % if the fields are stored in different tables?

Update One Field With More Fields From Another Table
I have a select count() that seams be ignoring one clause. Data:

Table:
ID CA-Char CB-Int
1 dsfsd 6
2 dsfsd 0
3 dsfsd 1
4 sdrtt 1

SQL is:
"SELECT count(id) FROM Table WHERE CA-Char = 'dsfsd' AND CB-Int > 0"
Returns 3 - Should be 2

Listing Table Content By Fields
i've got a mysql db with a table called tshirts . Im trying to display all the shirts by category on one page in a table 2 colums wide, been told i need a do while loop ? ive looked up on php.net but didn't really make much sense to me.

while($row= mysql_fetch_array($rs) )
{
$listt .="<td width=25%><div align=center>".$row["tshirtname"]." ";
$listt .="<img src=".$row["mainpicture"]." >";
$listt .=" ".$row["tshirtdesc"]." ".$row["price"]." ";
$listt .="</td>";

the above code just displays all the shirts in a long horizontal line, thats as far as i can get, ive not got that much knowledge of php or mysql so sorry if this is a stupid question,

Mysql5.0 And Phpmyadmin Cant Add Fields To Table
I have loaded the phpmyadmin the latest version to manage my databases in mysql5.0. The problem I am having is when go to create a table and attempt to add fields to the table I get an Error MySQL said The field count is empty

I aslo just tried to delete the database and get this error message.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I been looking for last few days on a way to fix this problem. I have only been able to create database but that is it. I am the only user with all privileges.

How To Get All Distinct Words From Many Fields Of A Table
I have a table with many text and varchar fields and I would like to get
all distinct words from these filelds.

For example:
table Pet:
id int(10) unsigned
legende text
notes varchar(255)

#id #legende #notes
1 mysql is very very good' is it true
2 just mysql test

I would like to get a list (sorted if possible) like:
#word #occurence number #id
good 1 1
is 1 1
just 1 2
mysql 2 1,2
test 2 1
very 2 1

Mysql.event Table Has 18 Fields Instead Of 22
I tried to find out all fields of mysql.event table but there seems to be no info. Error message on 5.1.22 under windows: Column count of mysql.event is wrong. Expected 22, found 18. The table is probably corrupted.

2 Fields Combination Unique In Table...
So I need two unique fields in my table, but they should not be unique separately, just a combination of values in this two fields... Let's say I have table with fields book, page, text... I can have more then one book #1, more than one pages #1 but only one combination of book #1 and page #1! But I have page #1 for each book!

How to restrict this in MySQL?

JOINing From Multiple Fields To One Table
I have two tables in a MySQL database. One has the publication data (title, ISBN etc), and the other has the data on the authors that wrote it (first name, second name, etc.)

I am trying to produce a single recordset that will show the authors' names (first name + a space + second name). The staff.staffID corresponds to publications.author1, publications.author2, etc.

The problem I have is that there are potentially 5 authors for each publication record, so when I try to LEFT JOIN them all them to the staff table, I get a '1066 not unique table / alias: staff' error - I can see why this is happening (becuase of the 5 left joins onto the same table) but how should I change the code to get the results that I need? Code:

What Is The Maximum Possible Number Of Fields In A Table?
What is the theoretical and practical limits of number of fields in a table?
Say I have 1000 or 10000 integer fields in a table, does the query processing speed decrease?

Simple Table With 3 Text Fields
I am trying to setup a simple table with 3 text fields. I amusing Myphpadmin to do this, this is my sql statement:

CREATE TABLE `zones` (`country` TINYBLOB NOT NULL, `rate` TINYBLOB NOT NULL, `time` TINYBLOB NOT NULL, PRIMARY KEY (`country`))

But the extremely unhelpgful error message gives me:

BLOB/TEXT column 'country' used in key specification without a key length

I have tried specifying a length but then get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(25) NOT NULL, `rate` TINYBLOB NOT NULL, `time` TINYBLOB NOT NULL, PRIMARY KEY (`country`))' at line 1

Order Of Fields In InnoDb Table
During my extensive reading a couple of weeks ago,I read that MySQL tables should have their fields in a specific order.All the foreign keys should be first followed by the 'hard data' fields.

explain if this is, in fact,necessary and, if so, why.I can't recall where I read it or I would go back there again.

said it should be like this:

unique_id | business_id | address_id | first_name | last_name |
00001 | 00005 |00003 | Joe | Soap |

Counting Fields In A Table

is there a way to get the field count in a table in MySql?