MYSQL :: Select Count From Multiple Tables

Select Count From Multiple Tables
I want to select persons from a person table, and count from an "events", "trainings", and "leads" table, but I'm not able to figure out how to select distinct so that I get one person each and a count for how many times that person appears in each other table. My statement looks like this right now:

SELECT person.name, count(leads.lead_id), count(events.event_id), count(trainings.training_id) FROM person LEFT JOIN leads ON leads.person_id=person.person_id LEFT JOIN eligible ON eligible.person_id=person.person_id LEFT JOIN events ON events.event_id=eligible.event_id LEFT JOIN trainings ON trainings.event_id=events.event_id GROUP BY leads.lead_id, events.event_id, trainings.training_id

Is it even possible to do what I want?

How To Count() From Multiple Tables
I have 3 tables as follows:

Lesson:

lesson_id | lesson_name |
-------------------------|
1 | Lesson One |
2 | Lesson Two |
3 | Lesson Three |
4 | Lesson Four |
--------------------------

student_lesson:

lesson_id | student_id |
----------------------|
1 | 44 |
1 | 45 |
1 | 46 |
1 | 47 |
----------------------|

class_lesson:

lesson_id | class_id |
-------------------|
1 | 901 |
2 | 902 |
3 | 903 |
4 | 904 |
-------------------|

The following query:

SELECT crs_lesson.lesson_id, lesson_name,
count(crs_student_lesson.student_id) AS student_count,
count(crs_class_lesson.class_id) AS class_count
FROM crs_lesson
LEFT JOIN crs_student_lesson USING (lesson_id)
LEFT JOIN crs_class_lesson USING (lesson_id)
GROUP BY crs_lesson.lesson_id

Produces the following result:

lesson_id | lesson_name | student_count | class_count |
------------------------------------------------------|
1 | Lesson One | 4 | 4 |
2 | Lesson Two | 0 | 0 |
3 | Lesson Three | 0 | 0 |
4 | Lesson Four | 0 | 0 |
------------------------------------------------------|

This shows that the second count is always set to the same value as the first count even though the values should be different. Does anyone know how to obtain the correct values?

COUNT(*) Across Multiple Tables
I have a query to the effect of:

(SELECT COUNT(*) FROM tbl_a WHERE id="1234" ...) UNION ALL (SELECT COUNT(*) FROM tbl_b WHERE id="1234" ...)

This gives me two numbers--the counts of the relevant rows in each table that share the id. However, I would like to return the sum total of rows in *both* tables. Can this be done in one query with a SUM of some sort?

I'm using v4.0.14-standard.

I've done a quick search about the forum and couldn't quite find a solution to this.

Multiple COUNT() In SELECT Statement
SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,5) = "Small"
) OR (
LEFT(s01_Options.prompt,2) IN (28,30)
)
)

SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,6) = "Medium"
) OR (
LEFT(s01_Options.prompt,2) IN (32)
)
)
And one for Large and Extra Large...

I would obviously like to combine all 4 queries in to 1 so that I can get my 4 counts in a single,

Select With Multiple Tables
I have a select coded like: "SELECT * FROM Table1, Table2, Table3 WHERE Key1='Joesmith'"

the query works correctly except that both Table1 and Table2 has a column named "Points"

What is the proper way to retrieve the correct column named Points? I tried "Table1.Points and that failed. I changed the order of table names and then I retrieved the one I wanted (ie. Table2). It appears that if two tables have the same colun name that the one referenced is the last one listed.

Select From Multiple Tables
I have multiple tables named like this wpctchie, wpctchee, wpcbasfr, wpcbasth etc. The idea is that admin will be able to search all tables beginning "wpc" (all tables), superusers will be a able to browse tables beginning "wpctch" ("wpctchie" and "wpctchee") and users will be able to search only one table e.g "wpctchie"

Is it possible to do a search such as

SELECT * FROM wpc% (for admin)
SELECT * FROM wpctch% (superusers)
SELECT * FROM wpctchie (well this just works anyway!)

Does that make sense what i am trying to do?

Select From Multiple Tables
What is wrong with this query?Code:

SELECT * FROM armor, etcitem, weapon WHERE armor.item_id = '57' AND weapon.item_id = '57' AND etcitem.item_id = '57';

Select Statement From Multiple Tables
how can I do this type of join:

table1: id, value1, value2
table2: id, value1, value2
table3: id, value1, value2

the "id" is a product code
the values are new & used pricing

I need a select that can join all this data, and fill 0's if an id is
not listed in a gven table, then output to a file like so:

id, table1.value1, table1,value2, table2.value1, table2.value2,
table3.value1, table3.value2

obviously, if I join on all the ids, it only will output the values
that have the same id in every table. how do I get that plus all the
values that are unique to each table?

SELECT From Multiple Tables With Different Columns
I am trying to combine 2 SELECT queries so that I can order them to display in proper date order.

The queries I am using are below. My question is, how can I join together these queries so that they will mix the news and events together to display them in order instead of displaying the news items first in date order then displaying the events beneath them in their order.

$query = "SELECT * FROM news_articles WHERE (date_full >= '$days_back' AND date_full <= '$days_ahead') AND (news_type = '$news_query') ORDER BY 'date_full' DESC";

$query = "SELECT calendar.* FROM calendar LEFT JOIN events_type ON events_type.event_id = calendar.id WHERE calendar.date_full >= '$eventdate_time' AND date_full <= '$days_time_ahead' AND STATUS = 'active' AND (events_type.type_title = '$events_query') ORDER BY 'date_full' ASC";

Anyone got any ideas?

Select And Join For Multiple Tables
I have five tables in my database, there are actually NO common fields
between them, not even a KEY or ID or anything like that, except for
the "body" of a blob field. and that text is not identical, just a
portion of that text is identical.

each table has 5 fields, all different except the blob, which is
called "message", so normally I use something like:

select * from table1 where message like '%apple%';

to query this table, and the same goes for table 2, except the blob is
different, table 2 normally is like this:

select * from table2 where message like '%customerid=453%';

It's impossible to change the data in these fields (which would be the
best option), but there is one common element between them in the
message blob.
What I want to do is something like this:

select * from table1, table2 where message like '%order=100%';

however only one table will have that order, either table1 or table2,
but never both, and theres no way to tell which of the tables will
actually have the text.

In other words, I want to search a bunch of tables for common text
without having to actually submit the query five times, because the
list of elements to search is about 25,000 items... I'd rather submit
25,000 queries than 125,000 queries.

Select All Rows Greater Than... (multiple Tables)
Edit: Using MySQL 4.0.27.

I have a standard forum database, and am trying to extract data from it. I want to extract "Last 7 topics you have posted in", and need some SQL help!

The data returned should list the last 7 topics you have posted in. The last topic you have posted in will be at the top.

A topic row can have many associated post rows (many posts in one topic).

This SQL query works for this purpose: (Assume userid = 2)....

Efficient SELECT From Multiple Tables With Same Formatting?
Let's say I have 2 tables with the exact same formatting (field/column names, etc) -- the only difference is the name of the tables.. one is called "table1" the other is called "table2"

If I wanted to query both of the together is this legal and good coding practice?

"SELECT id, headline, permalink, body FROM table2007_11, table2007_12 WHERE id=23"

understandly I get this error: Column 'id' in field list is ambiguous"

is there a better way to do this than printing out each table name for each field individually? (ie. id. table2007_11, id. table2007_12, etc)?

Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine)

Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do)

insert joomla2.jos_magazine_articles
(joomla2.jos_magazine_articles.name,
joomla2.jos_magazine_articles.article_id,
joomla2.jos_magazine_articles.category_id,
joomla2.jos_magazine_articles.catid)

select
joomla2.jos_content.title,
joomla2.jos_content.id,
joomla2.jos_content.catid,
maxdev.jos_content.catid

Accessing Multiple Tables With A Select Statement
I am writing a select statement, that says something to this effect. Select * from texas, california, oklahoma; Everything in each table is going to be the exact same. as far as city, name, and zip...they are the titles of my fields. But when I go and look at this file, it just shows everyone from oklahoma. If take out out oklahoma, then it shows everyone from california but not texas. What is the proper way to do this?

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.

Select Statement With Multiple Counts/multiple Joins Trouble
SQL
SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count
FROM duo_groups as g
LEFT JOIN duo_group_members as m on m.group_id=g.group_id
LEFT JOIN duo_users as u on u.user_id=m.user_id
LEFT JOIN duo_videos as v ON g.group_id=v.group_id
LEFT JOIN duo_video_comments as c on v.video_id=c.video_id
GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username
Will return

My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.

Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful!

Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better.

I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns.

For example, I'd want this:
1-1,1-2,1-3,1-4,1-5
2-1,2-2,2-3,2-4,2-5

To become:
1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5

The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like:

SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345

This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).

Multiple Count()s
I have a table with links, a log table to record clicks, and a vote table
to record votes on the links.

Is it possible to get the link data, the click count, vote count and vote
total in one query?

I can get the click count OK, but as soon as I add the vote count to the
query, it multiplies them; e.g. for a link with 14 clicks and 2 votes, it
tells me 28 for both figures. Here's the query:

select lnk.LinkID, URL, LinkTitle, Description, count(log.LinkID) Clicks,
count(Rated) Votes
from fn_links lnk
left join fn_log log using (LinkID)
left join fn_rate vot using (LinkID)
where CatID = $CatID
and Status = $Active
group by lnk.LinkID
order by Created

Then of course I want to include sum(Rating). I tried grouping on
different things, but it makes no difference.

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 Multiple Values
I have a very basic question but I havent found answer to it anywhere.

I have a table that has following columns: date, answer1, answer2,
answer3, answer4
and entry: 2007-1-1, 'a', 'b', 'a', 'c'

What I want to do is count number of character "a":s as an answer.
Normally (if using COUNT, MATCH..AGAINST) mysql returns rows it finds.
I want to find out how many times this character "a" appears in a
record.

Count Multiple Comlumns?
i want to count several columns independently, so i tried:

SELECT count( a.id ), count( b.id )
FROM inbox AS a, forummessages AS b
WHERE a.id = '1' AND b.id = '1'

but that just produces:

count (a.id) - count(b.id)
58 - 58

true numbers are:
count (a.id) - count(b.id)
29 - 2

but i want to count the total number of messages in a where id = 1 and total number of messages in b where id = 1. Anyway to do this with just one sql statement?

Count Of Multiple Fields
I have a database that stores some stats for a hockey league in the following manner:

Field names are GP, G, A

Whenever a player Plays a game, his ID is added to the column GP while other columns go to 0
If a player scores a goal, his ID goes under G, the guy who assisted goes under A

What I want is a query that can give me the count for each ID that shows up in GP for GP, G and A

Is there an easy way to do this with just one statement? Right now, it's really messy since I have to loop through each player ID in GP and make individual queries to count GP, G and A because I can't figure out an easier way and I don't want to rebuild the DB

Multiple Count From The Same Field
I have been having some trouble with the query below.

select a.advisor_ao as 'AO',
case c.existing_client when 'feps' then count(c.existing_client) else 0 end as 'total'
from advisor_tb a left join company_tb b on a.advisor_id = b.advisor_id
left join comp_seminar_ca_tb c on b.company_id = c.company_id
group by a.advisor_ao

I am not looking for an answer just wanted to know if my query is incorrect because if it is not thean it's my table structure

Multiple COUNT() And GROUP BY
I think I'm on the right track, but I could use some help. I have a simple database with one table that contains observations of animal behavior. Columns include date, time, and animal_type.
I want an output that shows the number of times each animal type was observed, grouped by date. Something like this (sorry about the formatting)

date cat dog mouse
2004-1-1 1 2 3
2004-1-2 2 3 5
2004-1-3 1 2 3

I've gotten as far as getting one column, which would be this query:
"select date, count(date) as cat from observations group by date"
Can any one tell me how to get the rest?

Extract Multiple Columns From Multiple Tables
For hours and hours I've been trying to work out how to write the sql query to extract some data from some tables, but with no luck.....

Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10 .

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)

Multiple Count Queries -> One Report
I'm getting a bunch of data out of a database in the form of a lot of COUNT statements eg:

SELECT COUNT (*) AS "Value1" FROM tablename WHERE x=1;
SELECT COUNT (*) AS "Value2" FROM tablename WHERE y=5;
etc ... there are about 30 queries.

I'm using PHPmyAdmin to do this. Currently I have to put each statement in separately: If I put the whole load of them, PHPadmin only displays the last value.

I don't need a great deal of detail, but if someone can suggest a way I can accomplish this so I run one multi-line query and get all my values at once that would be great. Perhaps there is a better tool than phpMyadmin? Or maybe I should be looking at VIEWs or STORED PROCEDURES? If you can paint the broad outlines, I can fill in the gaps.

Count On Multiple Table Joins
I have 3 tables: COURSE, USER_COURSE, COURSE_TOPICS

What I am trying to do is select all the rows of COURSE and have a count of all users on each course and a count of the topics assigned.

Id normally write that as an inline select in oracle but it seems Mysql doesnt work in quite the same way!

Using an outer join on just COURSE to USER_COURSE i got it to work:

select c.*, count(uc.course_id) as no_of_members from course c
LEFT OUTER JOIN user_course uc ON c.course_id = uc.course_id
group by course_id;

So following that example I then added an extra join to COURSE_TOPIC to get the count of topics:

select c.*, count(uc.course_id) as no_of_members, count(ct.topic_id) from course c
LEFT OUTER JOIN user_course uc ON c.course_id = uc.course_id
LEFT OUTER JOIN course_topic ct ON c.course_id = ct.course_id
group by course_id

but it returns the wrong figures for both counts!

Using Count(*) With Distinct To Determine Multiple Copies
I have an sql table 'messages' that stores peoples messages, in the form below: (Cname = conversation name)

User Recipient Cname Subject Message
Fred Bill Weather Rain Levels I think they are low
Fred Harry Traffic Congestion Its getting worse
Fred Bill Traffic Congestion Its getting worse
Fred Harry Money Savings The interest rates dropped
Fred Bill Money Savi

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.

Multiple Smaller Tables V.s. Fewer Bigger Tables
What is the best way to store data in a database? Multiple smaller tables (which means many Inner/Left Joins when fetching the information) or fewer bigger tables (which means few or no Inner/Left Joins when fetching information).

Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH
LINUX)and I'm wondering which is the best option to do :

I currently have one file for each of my modules, (I'm using filepro plus) ,
so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with
their own fields.

1) Should I create multiple databases on MySQL

2) Should I create a single database on MySql and with multiple tables ?

Which one of these options is the best and more safer ?

Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time.

I have to decide if I should use :

1)one database with one table for all users.
2)one database for each user.
3)one database with one table for each user.

I would really appreciate if you can tell me what choice is best and why.

Using Count Through Different Tables
I have 3 different tables with these structures:

TABLE: users
FIELDS: id, username

TABLE: pedidos
FIELDS: id, user

TABLE: items
FIELDS: id, pedido

When an user buys an item, the tables pedidos and items are updated:

PEDIDOS:
-set id to id+1 (autoincrement)
-set user to the id of the user who has bought the item

ITEMS:
-set id to id+1 (autoincrement)
-set pedido to the new id of the last entry of the table pedidos (the one we just added)

Now, my problem is that I want to print the users that have bought more than 50 items and the total number of items theyÂ´ve bought. To do so I need to print the username of the users for whom the COUNT() of items is larger than 50...But I need to use pedidos to relate the items and the users (something like "pedidos.user=users.id AND items.pedido=pedidos.id").

Count Over Several Tables
I have several tables in the form

t1:
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name | varchar(20) | | | | |
...
+----------+---------------+------+-----+---------+-------+

t2
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name | varchar(20) | | | | |
...
+----------+---------------+------+-----+---------+-------+

and so on (each table containing a column name). Now I want to have a list how often each name occurs in all tables. For example, if the name 'Tom' is contained 3 times in table t1 and 11 times in table t2, I need the tuple ('Tom',14) as a result.

I need to do this with at least 3 tables which contain the column name, and I need to do it with MySQL 4.0, so I can't use views or subqueries... Does anyone know how to do it?

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?

SELECT Count
how to use SELECT Count.

Select *, Count
I want to perform a select query, but it doesnt return the data i have in mind. The result should be all info in 1 row with the highest date plus a total of game_id
This is what works best sofar.

SELECT * , COUNT( game_id )
FROM `battle`
WHERE game_id =4
GROUP BY game_id
ORDER BY b_date DESC
LIMIT 1

Yet this doesnt return the last date but the first. Everything else i tried the count function returned 1 even though it should be 20 for example.
How can i solve this?

SELECT COUNT(*)
I heard SELECT COUNT(*) can take a lot of resources if your counting a table with lots and lots of rows (hundred thousands, millions).
What if you add A WHERE clause to it? So something like:
SELECT COUNT(*) FROM table WHERE pid = ?
(pid is a index too btw)
IF adding a where clause, does it still scan the entire million plus rows, or only scans what is returned from the WHERE clause?

SELECT COUNT Or SUM()
Is there a difference between these two functions when your trying to gather how much a item appears in a database?
For example, which one will be better for a database w/ ~150,000 rows.

SELECT COUNT(category_id) FROM Threads WHERE category_id = '2'

Will return ~50,000 rows matching category 2

OR

SELECT SUM(category_id = '2') AS total FROM Threads
total will be ~50,000 rows also.

Select Count
I need to do the follow SQL..PHP Code:

SELECT COUNT(t.id), COUNT(p.id) FROM threads t, posts p

and add the two counts together... within the SQL query can i do this?
or do i just have to return the arrays and added the results seperatly.

COUNT() 2 Tables In 1 Query
So I have these two queries:

$result= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_main where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

$result2= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_pop where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

They output data like
Month Year, Total Views
So I can see how many total times a page was visited that month for a specific user_id.

The problem is I'm tracking 2 sections of the site "main" and "pop" which pop is a unique page that pops up when something is clicked.

I tried EVERY way to join them together but the count(user_id) doing a LEFT JOIN kept adding the count()'s from both tables together.

Is it not possible to do a count() on two tables in 1 query?

Here's one of the many combined I tried.

$sql = "SELECT a.user_id,b.user_id,YEAR(a.add_date),MONTH(a.add_date),COUNT(a.add_date),COUNT (b.add_date) FROM clogsyn_track_main a LEFT JOIN clogsyn_track_pop b ON a.user_id=b.user_id AND YEAR(a.add_date)=YEAR(b.add_date) AND MONTH(a.add_date)=MONTH(b.add_date) WHERE a.user_id='$uid' GROUP BY YEAR(a.add_date),MONTH(a.add_date)";

Can anyone help me here or is it not possible to do it in 1 query?

Count Records From Two Tables
I have two tables and I want to count the number of records where field1 = 1.

tbl1    field1
           1
           2
           1
           0

tbl2    field1
           0
           0
           1

Result should be 3

Can someone help with the right coding?

Three Tables Nested Count
I have three tables: Customer, Orders and Items.
If I search on customer I want to display:
1. how many orders that customer has.
2. How many items in these orders that are pending, delivered etc. (the status is specified in Item.item_status)

I want it to look something like this
Cust ID, Name, No Orders, pending item, deliverd item, etc
1234, Smith..,3, 2, 1
1235, Jonson, 1, 3, 2
1236, Olsen, 3, 0, 6

Select Count Where Entries Are < 4
I want to count how many users have 4 or less entries in the table, so, so far i have
SQL
SELECT COUNT (*) AS fourLOCS FROM
(SELECT DISTINCT locsLog.locStudentID FROM locsLog)
which counts all the entries in the locsLog table and groups them by studentID so tells me how many users have made entries.
How do I limit this so it only gives me a total number of users who have made 4 or less entries. I think I'd need WHERE after the nested SELECT statement, but I don't know what I'd put in it. I tried
SQL
SELECT COUNT (*) AS fourLOCS FROM
(SELECT DISTINCT locsLog.locStudentID FROM locsLog)
WHERE fourLOCS <= 4
but had an error message (I'm doing it with an Access database with ASP and the message was 'Too few parameters. Expected 1').

Select Count(*) Timing Out
I'm doing a "select count(*) from table1" and it takes forever, more then 3 minutes, for the result to come back. I'm running MySQL 5.0.19 on RedHat, I know my table has 51M records. When I run a "show table status where name='table1';" it returns the results in a few ms and gives me a record count. My table is defined below, I've tried forcing use of each index individually and forcing no indexes and all combinations and always the same result. Server is an intel based 64bit cpu with 16GB ram with pretty much nothing else going on......