MYSQL :: Subquery And Counting

Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query.

First Query
select

ACCOUNT_ID,
ACCOUNT_TYPE_C,
PAT_ID

from PAT_ACCT_CVG

where ACCOUNT_TYPE_C in (120103,120104,120101)

Second Query

SELECT

PAT_CVG_FILE_ORDER.PAT_ID,
PAT_CVG_FILE_ORDER.LINE,
COVERAGE.COVERAGE_ID,
COVERAGE.CVG_EFF_DT,
COVERAGE.CVG_TERM_DT

FROMPAT_CVG_FILE_ORDER
LEFT OUTER JOIN COVERAGE
ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID

Where coverage.payor_id = ?'

Third Query

select

TRAN.ORIG_SERVICE_DATE
TRAN.TRAN_TYPE,
TRAN.INSURANCE_AMOUNT

from Tran

where TRAN.TRAN_TYPE = 1
and TRAN.INSURANCE_AMOUNT > 0
and TRAN.proc_ID in 1008,1009

(now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)

Sub Counting For Each Row
For each row in my table, I'm getting stuck doing a count. An extract of the table is as follows:

user_id | name | favourite_user
1 | "person 1" | 2
2 | "person 2" | 1
3 | "person 3" | 1
4 | "person 4" | 2
5 | "person 5" | 2

I'm trying to select all rows from the table, and at the same time, a count of how popular each person is. In two queries, this can be achieved by:

SELECT * FROM TABLE;
SELECT favourite_user, COUNT(*) FROM TABLE GROUP BY favourite_user

How can I combine the queries so that for each person I return their data and a count of their popularity?

Subquery Help
I am using mysql 4.0 which does not support subquerys.How can i rewrite the below query using joins for mysql 4.0
select * from t1 where t1.eid not in(select eid from t2)

Subquery Help.
I am using the following but then I rembered I am getting the readyPrinted_id from the select statement!! How can I fix it so it gets the readyPrinted_id and then performs the AND with that same ID?

SELECT
`sub_name`
, `readyPrinted_name`
, `readyPrinted_id`
FROM sub s,
readyPrinted r
WHERE s.sub_id = $sub_id
AND s.readyPrinted_id = r.readyPrinted_id
AND r.readyPrinted_id = readyPrinted_id

Using NOT IN In A Subquery
I am using MySQL version 4.0.27-standard

everytime i use "not in" in the subquery:

LIKE ANY + Subquery
I'm trying to get the following statement to work:

SELECT * FROM discountItems di
WHERE di.name LIKE ANY
(SELECT lsw.word
FROM ifDefinedSearchWords dsw
join ifLinkedSearchWords lsw on lsw.fIFEntityID = dsw.fIFEntityID
WHERE dsw.word like 'schoggi')

It is supposed to find some words in a subquery as one row, and then search another table for records matching any of those words. I get the following 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 .....

Subquery
Hi, wanted to use a subquery, as in:

SELECT * FROM pending WHERE username = (SELECT username FROM ratings);

But have learned subqueries are only available in MySQL 4.21 or later...

So...

I've tried to use the following query:

Code:

SELECT ratings.username,pending.username FROM
ratings,pending WHERE ratings.username="jjfjunk" OR
pending.username="jjfjunk";

But this returns an empty set, even though I know that the username exists in the ratings table.

Subquery
Can help to find any sytanx error from the following command?

select IMG_ID
FROM image
WHERE WORM_Name IN (SELECT WORM_Name FROM worm WHERE WORM_Age='adult')

I don't know where is wrong of above, but always got the sytanx error messange after I type in the above commands.

Subquery Help
I am pretty sure I have the syntax totally wrong.. but you can probably get an idea of what I want to do.

Code:

INSERT INTO library (tape_id,number) VALUES('DDFA3','433145')
WHERE DDFA3 NOT IN (select tape_id from library where used = '1')";

I'm basically wanting to make sure that no record for that tape_id exists that is set to "used" before I insert another record.

Subquery
I'm having trouble getting the results I need from an sql query. I believe I can accomplish my task with a subquery however I can't get it to work. I have a table of book titles and a one to many table of books read (that holds many people via ID and the book via ID they read). Now I need to get a list of books left to read by a particular person from my join. So the query starts with a join between the books table and the books read table to get the book titles. I use a left join to get all the book titles even if they haven't been read. However I need it to filter out the books that the particular person has read and leave me with a list of books left to be read by the particular person. Since I'm using a join I need it to ignore the records of the other people so that it will include does books in the results while excluding the books that the particular person has read.

Sum() And Subquery
SELECT domainTable.name as domain, sum(NetworkTraffic.upload) as Upload, sum(NetworkTraffic.download) as Download, sum(NetworkTraffic.upload) as Upload2 sum(NetworkTraffic.download) as Download2
FROM `NetworkTraffic`
LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId
WHERE NetworkTraffic.protId=2 AND date between "2005-10-11" AND "2005-10-18" GROUP BY domainTable.name

I am using the left-join above to merge two tables (NetworkTraffic and domaintable) on domainId to find which domain to present. By doing this i summarize the download and upload from NetworkTraffic between 2005-10-11 and 2005-10-18, however i want to be able to summarize the traffic from one day, say 2005-10-12 in column 3 and 4 (Download2, Upload2). Is it possible to make a subquery in the sum-function or should i take another approach? I am using mysql server 4.0.18-max-debug

Counting Records
Got a question for you that is really stumping me. I have 3 tables

job_jobs
------------
job_id
job_name
.
.
.

job_cats
-------------
cat_id
cat_name
.
.
.

job_jobsToCats
-------------
jtc_id
job_id
cat_id

***************

ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get

accounting (12)
admin(2)
etc
etc

Counting Like A Human
I have a query that does an ORDER BY area ASC. When it does the order by it is ordering by how a computer counts rather than how people count.

As an example here is some of my data ....

Counting Query
Is there a MySQL function that i can use to just count the number of rows in a table instead of listing all the IDs and running a command on the result?

Counting Rows
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/itsyouma/public_html/staff/memberlist.php on line 6

<?php
$SQL_statement = "SELECT * FROM users";
$resultset = mysql_query($SQL_statement);
$count_rows = mysql_num_rows($resultset);
?>

Counting Rows
I have a table containing

Article | State
-------------------
1 | 10
2 | 10
1 | 5
1 | 5

and I want to count all Articles in one row with the different states

SELECT Article, COUNT(State=10) AS Ordered, COUNT(State=5) AS Free FROM...... GROUP....

but this SQL doesn't work (on my Server 4.0.20) I don't know if it work above but in the docu I can read somthing COUNT(expr) my be it works in 4.1

but is there another way that works wit 4.0.20?

Hour Counting
I keep in a ltable 'log' the time and date each user loged-in, and the time and tahe each user loged-out... among other things. What I want to do is a report to show how long each user was loged in the system.

I'm using MySQL 4.0.15.

I was thinking if I could do it with a single query but I cant figure out how.

Table log: ....

Counting Query
I need a slect statement to return the count of rof rows if a particular field matches a criteria similar to the COUNTIF function in Excel. We are using mySQL 3.23.58. Is there a way to do this and if so how?

Counting Days
How can I make a query such that in the "WHERE" part of my clause, I want to
put something to the effect that, say, X days have elapsed since a given
date.

For example, suppose in my table, I have a boolean field and a date field.
I want to create a query asking for those rows where X days have elapsed
from the date field in a row and the boolean, say, is false.

Do I need to create a third field, called, say "X" for how many days have
elapsed between the date field and today, and update every row in the table
every day?

Counting Function
I have a table with a list of names and some are duplicate and I would like to count them?? It's like this:

Drew
Simon
Alli
Tim
Beth
Alli
Simon
Drew
Simon
Simon
Alli

Then to export:
Drew - 2
Simon - 4
Alli - 3
Tim - 1
Beth - 1

How could I go about asking this query?

SELECT COUNT(name) FROM table

??

Counting Correctly!
I want to ORDER BY on a column. Straightforward until I discovered that MySQL doesn't count the same way I do.

My values are

TA1
TA2
TA3
TA4
TA5
TA6
TA7
TA8
TA9
TA10
TA11
TA12
TA13
TA14

When I ORDER BY, MySQL thinks TA10 is less than TA2,

Resulting in

TA1
TA10
TA11
TA12
TA13
TA14
TA2
TA3
TA4
TA5
TA6
TA7
TA8
TA9

I know a simple solution would be to rename my codes TA01, TA02 etc, but I can't as the values are an external code that we have to use.

Is there a way I can get MySQL to ORDER BY correctly on these values?

Counting Unique
I have the following code...

$query = "SELECT DISTINCT newsid FROM comments";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){

which works fine. My questions is there any way to determine which newsid shows the most amount of times. I'm trying to make a "top5" sort of things and it would make it much easier.

Virtuel-Counting-Row
I use the database mysql v.4. My problem is... I have a select like:

select * from user where language = "de";

the result are then:
id name
===================
1 max
5 tim
99 otto

so and what i need is a virtuell row (pos) with a counting index of the result:
id name pos
=================== ===
1 max 1
5 tim 2
99 otto 3

how can i make this with mysql? My first idea was to made that with views but mysql can't do that!

Counting An Item
I was wondering if someone could tell the correct way to go about using the count statement within a select query.  I want to count students attempts at taking an exam but only show users who have done it more than 3 times...

Table: Student_Attempts
Stu_Name    Attempts
Bob             3
Mary             6
Sam             8
Jane             1

Its the more than 3 times part that I'm stuck on.

Counting Usage Per Day
I want to record how many times an application is used in a day. Is it possible to create a table with a date field that automatically creates a new record each day with the present date as the primary key.

Counting With LIMIT
I'm trying to retrieve the number of records found using an offset (with LIMIT), however I get no results back eg.:

SELECT count(propID) FROM properties WHERE propAvailable=-1 AND propType='s' LIMIT 10,10

Produces:

MySQL returned an empty result set (i.e. zero rows)

However, removing the LIMIT eg:

SELECT count(propID) FROM properties WHERE propAvailable=-1 AND propType='s'

Produces:

count(propID)
38

I don't, though, want a count of all the records. The LIMIT works without the count eg.:

SELECT propID FROM properties WHERE propAvailable=-1 AND propType='s' LIMIT 10,10

Produces

Showing rows 0 - 9 (eg. 10 records)

But I only want the count, not the rows - arggghh!

How can I get back a count of a subset of the data? Is there another way of syntaxing this query?

Counting Elements
I got sql query like this that worked fine on a server, then a moved my site to another server and it doesn't work.

UPDATE user
SET on_time = (SELECT on_time
FROM review
WHERE userID = '$userID'
GROUP BY on_time
ORDER BY COUNT (*) DESC
LIMIT 1)
WHERE userID = '$userID'");

I found that ORDER BY COUNT (*) is the problem but don't know how to get the most popular on_time value to update the user table. The query below doesn't work because I need to get only the on_time value, not on_time + count.

UPDATE user
SET on_time = (SELECT on_time, COUNT( * ) AS count
FROM review
WHERE userID = '$userID'
GROUP BY on_time
ORDER BY count DESC
LIMIT 1)
WHERE userID = '$userID'");

Counting Records
Ive tested the following in PHPMYADIM and it returns each record and how many times it occurrs.

CODESELECT gm, COUNT(*) FROM league_ws GROUP BY gm ORDER BY 'COUNT(*)' DESC

Counting Set Items
Is it possible to count a set of items?

Example count("2,3,4,5",",");

Would return 4

Count items based on the delimeter? Or just count set items.

Counting Occurrences
I have 2 columns: 'keyword' and 'Category'. For example:

keyword | category
--------------------------
shoe | clothes
shoe | sport
shoe | clothes

Now, I want to create this output table:

keyword | clothesFrequency | sportFrequency
-------------------------------------------------------
shoe | 2 | 1

Because shoe occurs twice in the category clothes and once as the category sport.
I am looking for an efficient method, because in my final application I will use 5 different categories and millions of keywords.

Counting Tables
i've been through this and a few forums and the docs, and don't seem to have come across a way of counting how many tables are in a database.

specifically while using a like 'sometable%' would be useful for me

is this possible?

if there is not a command for this, i see that

mysql>show tables like 'sometable%';

returns a table count at the bottom, maybe i can retrieve that somehow?

Counting Distinct
I have a field that contains filenames. I'd like to count how many times a certain file name appears in that field.
So, for example if I have 3 unique filenames, I would like to be able to see something like this:

file1 = 120
file2 = 109
file3 = 76

The first part seems should be:
select count(file) from download
But how to specify returning a count for each distinct file?

Counting Rows
both of these return the same error. "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource"

$checkip = "SELECT * FROM 'vote' WHERE ip='$user_ip', vote_id='$vote_id'";
$ipcheck=mysql_query($checkip);
$total=mysql_num_rows($ipcheck);

$checkemail = "SELECT * FROM 'vote' WHERE email='$email', vote_id='$vote_id'";
$emailcheck=mysql_query($checkemail);
$total2=mysql_num_rows($emailcheck);

Counting Total Row
I am trying to select the total row count from two different tables. I am getting a result back but it is returning the same count for both even though they have different amounts. This is what I have so far:-

SELECT COUNT(companies.comp_id) as tcustomers, COUNT(project_id) as tprojects FROM companies, projects

as you can see I have two tables, companies and projects.

Counting The Number
I'm having difficulty figuring out how count the number of each distinct value in a column.Say I have a column called 'score' that contains a score value of 1-4, how would I could how many 1s, 2s, 3s and 4s there were in this column?

Counting Rows
In my MYISAM table I have an index (Index_A) on 2 fields (Field_A,
Field_B). There are millions of rows in the table. The cardinality of
Index_A is 53. I think a query to count the number of rows that match
a pair of values for Field_A and Field_B should execute blindingly fast
(in the index count the number of leaf nodes for the B-Tree node
representing the indexed pair of values).

My query looks like:
SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1

Why does it take 20 seconds for this query? And how can it be made
faster?

Counting Occurrences
I have a table Users which contains the id and vote1, vote2....vote10. The data stored in each vote field is the id of a product they are voting for as their favourite 10 products out of lots of different products.

are there any functions in sql that allow me to count the occurrences of product ids in all vote1 and then vote2 and so on?
With thousands of products I dont want to have to count for each one, only the ones that have been voted for.

Counting Sub Categories
It is setup like this:

catID - PK
catName - varchar
parentID - integer

For example:
Accomodation (catID = 1) is a top level category (parentID = 0).
Shared Houses is a sub category of accomodation (parentID = 1)
Hotels is a sub category of accomodation (parentID = 1)

Say I have 2 records in shared houses and 1 in hotels, how would I count it so Accomodation has a count value of 3?

Counting Records
I am trying to figure out how to count a subset of the records returned from a query. I have a query that returns records that fit the criteria events_registrants.pgApproval='1' AND events_registrants.approval='1'. Out of those records, I need to COUNT(events_registrants.pgApproval) which works fine and I also need to count a subset of those records which also fit the additional criteria events_registrants.complete=1. The code below doesn't return the correct count for events_registrants.complete=1. It gives me the total count of all instances of events_registrants.complete (which includes those that are equal to 0) so how would I write.

Counting Clicks
I want to create a click counter. When ever a user clicks a link the counter updates. I am using PHP for server side scripting and mySQL as backend database.
If somebody could tell me about any mySQL database level procedure/trigger or PHP script or any CGI or pearl script for counting clicks,

Counting Records
I have three tables I am trying to do a count on to give me a total amount. The problem is that somes of the sales order numbers are unique to the other tables but I want to count the unique records as well as the records that are not in the other tables. Is there an easy way to do this?

Counting By Age/group
how would i go about doing something like getting ages from database and then grouping them into age group? eg 18-20, 20-24 etc.. I could do that using PHP (i think, i have logic thought out) but was wondering if it would be easier to do in mysql

Counting Results
i'm trying to find a way of counting the results in a mysql table. Say I have the fields
CAR_MAKE | CAR_MODEL | COLOUR |
Is there are way of saying
car_make=bmw
car_model=z3
colour=black
how many of these are there im my table

Counting Rows
I have a value '120' standing in column 'number' from a table called 'example'.

Let's say I want to know how many records have been before this 120..how should I 'count' that?

Exampledatabase 'example':
----------------
|Number| Word |
----------------
|13 | this|
|22 | is|
|120 | an |
|422 | example|
----------------
In this example my return should be 2(the 13 and 22 are the records counted).

Counting The Records
is it possible to do this in one statement:
I wish to order by a certain field and count the records before a specific record, so as to get a sort of ranking.

Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement?
so far I have:

select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date)

but this doesn't include the 'zero' days (days when there were zero messages).

Counting Records
I have a downloads system on my website, and for each download I make an entry into a log database, which contains the userid of the person who downloaded it, the fileid, and the date it was downloaded (stored in unix timestamp).
Now, I want to make a stats page, that shows how many file are downloaded each day. How do I group this query? I can't just do it normally via the date field because the value in each record will be different even if they are on the same day.
So, basically I want to count all records grouped by each day, although the date field on each record will be different. How do I do this?

Counting From Different Tables
I have a problem getting counts from 2 different tables, via joins from the parent table.

At the moment, I am doing this:

CODESELECT q.*, q.id as q_id, t.town, c.category,
  count(qa.id) AS answers
FROM qq_local q
  LEFT JOIN qa_local qa
    ON q.id = qa.q_id
      AND qa.active = 1
  LEFT JOIN towns t
    ON t.id = q.town_id
  LEFT JOIN q_category c
    ON q.category_id = c.id
  GROUP BY q.q
  ORDER BY q.q_id DESC