MYSQL :: Counting Rows

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?

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 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 Rows
I have an sql table (messages) that holds messages related to conversation names. I need help to formulate a query that extracts the number of unviewed messages for each distinct conversation.
For example, with the following 4 tuples:

cname viewed

weather viewed
weather unviewed
mobiles unviewed
weather unviewed

I would need the output as:
You have 2 unviewed messages in weather and 1 unviewed message in mobiles

The Absolute Best Way Of Counting Rows
I've written a pagination class in PHP that basically gets a query, and returns, let's say, the first 50 results, then prints a navigation bar with links to the pages (1,2,3 etc)

For that class, I need:

1- to know the total results from the query
2- to run a query with "limit 50"

Up to now I have been using mysql_num_rows($query) to count the number of rows and then running the "LIMITed" query, but to me this seems a waste of resources
Isn't there a better alternative for counting rows? I've tried using COUNT(*) but the script receives some really complex queries (sometimes using 3 tables) and sometimes returns "1" as the total number of rows.
I can't find a way to "standardize" it in order to let me feed it whatever query I want

What I mean is, I need a fool-proof way to feed it ANY query, and it will return me the number of rows

Counting Rows More Efficiently
I have a table with around 1-2 million comments. A comment is a little text message (255 characters max) that someone can add to someone's profile.

When a profile is loaded, the 10 latest comments are pulled from the database and shown. Below the 10 comments is a link to the rest of the comments and tells how many more comments there are.

Counting the number of comments is slow, too slow to be doing everytime a profile is loaded. The columns have an index and the keys are packed.

How could this be done more efficiently?

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 Rows And Grouping By Day
I have a table called 'msgs' which stores the text of log entries, together with a timestamp of when the message was generated:

CREATE TABLE `msgs` (
`time_num` int(10) unsigned NOT NULL default '0',
`message` varchar(255) default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to be able to see how many log entries were generated on particular days of the week for the past seven days: 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 Number Of Rows
Can anyone come up with a novel way of counting the number of rows in a table that contain no NULL values in the columns that each row contains?
Each row contains 200+ columns, so doing a "count" on each column, and then selecting the smallest value is not very practical.

GROUP BY And Counting Rows
SELECT id,count(*) as c FROM table WHERE id='$_GET[id]' GROUP BY id
The following produces:

1009 8
1010 9
1011 11

"c" is coming back with 8, but that's not what I want. I want the total number of rows returned which in this case would be 3. How can I restructure this query to return the total number of rows while still allowing me to GROUP BY?
Is this even possible? How should I proceed?

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 Number Of Rows Returned
I want to count the number of rows returned when I execute an SQL query. At the moment I am selecting them all and cycling through them:

$count=0;
$query = "SELECT * FROM swe_picgal WHERE cat="".$cat.""";
if ($mysql_result = mysql_query($query, $linkdb)) {
if (ExecuteQuery($linkdb, $result, $query)) {
while ($row = NextRow($result)) {
$count++;
}}}
print('Number of rows: '.$count.'');

Surely there is an easier way? I assumer mySQL will have a built in counting function?

Counting Number Of Valid Rows
We have many kinds of advertisers in our web service:
1.private
2. companies, of which some are members of local enterpreneur community
3. non-profitable communities

Company advertisers can select also a "line of business" while for others
its unavailable. We have a menu where we want to put all the lines of
business in alphabetical order WITH the information, how many companies
(that are ALSO members of local enterpreneur community) are in that line of
business. I made the query following some model. It looked like working,
but now I noticed it doesn't right results always:

SELECT business_lines.*,
IF(seller_id <> 'NULL' AND seller_type='company' AND
seller_membership="LocalEnt", count( * ), 0) as nr_of sellers_in_this_line
FROM business_lines
LEFT OUTER JOIN sellers ON line_id=seller_line_id
GROUP BY line_id
ORDER BY line_name asc

So I suspect that if not ALL companies int certain line are members, that
line results to enpty line. Is that true and how to fix this?

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?

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?

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

Subquery And Counting
I have a table called people in my database.

it looks like this

+----+------+
| id | name |
+----+------+
| 2 | bob |
| 1 | Jill |
| 3 | Jill |
| 4 | John |
| 5 | Jill |
+----+------+
I want to display the distinct name and how many times they are in the table
So like this

Jill - 3
bob - 1
John - 1

How would I go about doing this. I thought maybe something like this but not to sure

[code]
SELECT COUNT(name)
FROM people
WHERE name =
(SELECT DISTINCT name
FROM people);
[code]

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?

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