MYSQL :: Counting Occurrences

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

Query For More That X Occurrences?
I need some help with this query.

I have a table like this:
| word | date |

Now I would like to push from this table all words from the last 5 days, order by the number of occurrences, and with more than 5 occurrences.

I already have this working and the only thing i miss is "more than 5 occurrences".

SELECT word, count(word) as cnt
FROM words
WHERE DATE_SUB(CURDATE(),INTERVAL 5 DAY) <= date
GROUP BY word
LIMIT 20

List Occurrences In A Table
I have a table of STUDENTS structured like this :

Students (IdStudent, NameStudent, FirsNameStudent, AdressStudent, DateOfBirthStudent).

My question is : I try to list occurrences in this table concerning NameStudent, in order to check if there are several guys/girls with the same name.(I don't want to use the name for my query, only people who exist twice or more).

I tried this :

SELECT COUNT(NameStudent) AS Nombre
FROM Students
WHERE NameStudent='PEARSON';

^^ this query works but shows me '2' and suppose that you kwnow the name you're looking for.

Limit Occurrences Of A Particular Column Value
I need to limit the occurrences of a particular value in single column in the results of a SELECT. eg, if I have a table of EMPLOYEES with various columns including CITY and want to select a random selection of 100 EMPLOYEES but no more than 5 from any particular CITY.

Any suggestions on how to accomplish this within MySQL (rather than trying to do so in my code)?

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?

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

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

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?

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 From Multiple Tables
I have a question about my query. Hopefully I place it in the right section.
I want to count some values from several tables.
When I run this query, the count values are wrong. When I only count from table 'bestellingen' (orders) or 'facturen' (bills), the count values match with what they should be.

The query:
"SELECT l.naam AS leverancier, COUNT(b.bestellingen_id) AS 'totaal aantal bestellingen' , COUNT(bo.bestelorders_id) AS 'totaal aantal bestelorders', COUNT(f.facturen_id) AS 'totaal aantal facturen'
FROM leveranciers AS l ...................................

Counting The Same Field Wit Different Values
Is it possible to count the diferent values a field may have with a simple query?? Let's suppose this data:

----------------
FIELD
----------------
1
2
3
4
2
2
3
2

I want a query that returns this:
----------------------------
VALUE # HOWMANY
---------------------------
1 # 1
2 # 4
3 # 2
4 # 1

Is it posible to include a condition when counting?? COUNT((WHERE...))