MYSQL :: Count Rows

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() Rows With 0 Matches
I am using the code below to select all the 'centres' in the database that have 'sessions' today.

SELECT c.*, r.region, COUNT(*) AS count
FROM centres c, regions r, sessions st
WHERE c.c_id=st.c_id
AND st.session_date=CURDATE()
AND c.region_id=r.region_id
This works and the results might look like:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3

However, is there a way to get MySQL to also select centres from the database that have no matches in the sessions table? So the results will look like this:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3
centre4 / 0
centre5 / 0
centre6 / 0

Count Same Field In Rows
i need to calculate the number of the same IP address that appear in the database, how can i do that?
example of the database:
IP Address Date and Time
127.0.0.1 5/23/2007
127.0.0.1 5/24/2007
192.168.1.2 5/25/2007
192.168.1.5 5/26/2007
127.0.0.1 5/27/2007

Count Identical Rows
I've got a log table looking like this:Code:

user | action | time
| |
a | click | 2005-05-05
a | login | 2005-04-05
b | logout | 2005-03-02
b | click | 2005-01-25

And now I wanna display the statistics in a neat interface. But I'm having problems... is there a way to select from the above table and get rows like this:Code:
user | click | login | logout
| | |
a | 56 | 23 | 45
b | 45 | 2 | 0
c | 12 | 32 | 32

Perhaps this is more than I could expect from MySQL?

How To Get Count Of Rows Returned While Using Group & Having?
i'm using the having keyword to find certain set of rows and they are working properly.

but what i want is , i want to count the total number of rows returned by this query using mysql.

select name,count(date) from emp group by date having count(date)>3;
this returned returns all the names that have more that 3 entries in the same date. it returned 5 rows and how can i get the count of rows(5) the query returned .

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

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)

Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'.
About half the rows are 'yes' and half are 'no'.
Table is indexed on 'stamped'.
We just need the count on 'yes'.

SELECT count(*) from T where stamped='yes'

or <>'no' or >'n'... is very slow.

Is there a better approach / query to get a fast result?

Count Number Of Rows Returned?
this is my connect and everything code:

<?php // connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die(mysql_error());
$query = "SELECT DISTINCT category FROM $tuttable";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
echo ($row['category']." | ");
}
echo count($row);
?>

Notice "echo count($row);" How do i get this to work properly. It only returns one, because there is only 1 that it is working on. I want to return the count of all the rows that fit that query.

Select Count(*) Showing -1 Rows.
In one of my preliminary tests with mysql, the below statement is showing the following way. What does -1 rows signify in the table with the select count(*) query. Is this (-1 rows in the query result) an error condition to be taken care ?.

bash-2.03# ./mysql -u test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8 to server version: 5.0.18-max

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Code:

Count How Many Matching Rows Exsist
I am new to SQL Querries, can someone help me figure out how to get a result?

I have a column in my db called; emails

some of the email addresses in this column may contain similar domains:

person1@site.com
person2@site.com
person3@someothersite.net

I need the result to count and end up being 2 not 3

Not sure how to make the sql statement:
COUNT (*) FROM database_tabe WHERE email='$email' LIKE '%@%.%'

Not sure how to get this result...

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Count Or Select Only The Populated Rows In A Column?
I'm trying to COUNT the number of populated rows within a column named, 'column_03', but first instead of using COUNT i'm using SELECT to see where the numbers are coming from.
lets say i use the following code...

SELECT column_03
FROM table_name
WHERE table_name.column_01 = 'IN''
AND table.name.column_02 ='GENER"

the result is a total number of 10 records. i can see that 'column_03' has 6 rows that are populated and 4 rows are empty. now what i want to do is select only the 6 rows that are popluated, once i now how to do that i can then use COUNT. Any suggestions?

Limiting Rows Returned Based On Count
say if I have 3000 records in my db, and I want to get rows 200~300 when orderd by a specific field in this table, is there a easy way to do it?

I have something like:

select * from table_name order by ID desc limit ....

but I am not sure if this syntax allows me to do what I am trying to do .

Return A Count Of Number Of Rows Before Desired Row.
Anyone know how? find_in_set doesn't seem to be able to do what I want since I am not searching through an array but am actually running a query.

What I really want is to be able to select all until something = something. I've searched the realm of google but cannot come up with anything useful, and so I am wondering if maybe I don't know the right words to be looking for.

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

Count One Table's Rows From Multi Table Query
here are my tables (condensed)

FEEDS
feed_id
site_id

SITE
site_id
site_name

ARTICLES
article_id
feed_id
link

I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this:

PHP

$gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles
WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection)
or die(mysql_error());

The query does not use JOIN, ON and all that good stuff.

I just need the following variables to run through a loop:

site_id
the number of articles rows per site_id

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows.

I'm using my SQL version: 4.1.19

Any clue on why this isn't working?

INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` )
VALUES (
(
SELECT Project_Code, Grant_Code, FY, SUM( Capital )
FROM Grants_Temp
WHERE Project_Code = 'OSUT'
AND FY = '2006'
AND Claim_Month = '072006'
GROUP BY Grant_Code
ORDER BY Grant_Code
)
)

Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query.
Column count doesn't match value count at row 1

I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query.

The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:

Count(*) As Count And TotalCount Of Count
data in myTable1

(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4

data in myTable4

(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.

I have the following code.

code

(select left(time,2) as hour, count(*) as count
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the code above produces the following result.

result

(day) count
(10) 3
(10) 1
(11) 1
The following would-be code doesn't work correctly, but it will show what I want.

would-be code

(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the following is my target result.

target result

(day) count totalCount
(10) 3 5
(10) 1 5
(11) 1 5

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Difference Between Count(*) And Count(1)
What is the difference between count(*) and count(1)?

Difference Of Count(*) From Count(1)
Is it true that count(1) is more efficient than count(*)? They say that count(*) still goes through all the records without needing to.

How To Fix "Column Count Doesn't Match Value Count At Row 1" ?
I've been trying to install a portal to my installation of Invision Power Board, and I keep coming up with this problem. I post here for two reasons: firstly is that no one is responding over at the portal creator's thread and I need help, second is that this error looks like a mySQL error that could be solved without the need of the creator if possible.

Here is the error returned:

mySQL query error: INSERT INTO `ibf_portal_box` VALUES (1, 'lang.last_topics', '<box><menu>10,20,30,50</menu>
<topics_show>10</topics_show>
<order>last_post</order>
<exforums></exforums>
</box>', 0, 'middle', '*', 0, 4, 'last_topics', '1')

mySQL error: Column count doesn't match value count at row 1
mySQL error code:
Date: Tuesday 21st of December 2004 08:43:10 PM

I am very new to mySQL and don't really understand it. What is wrong with the above that it is trying to insert, and how is it fixed?

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

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.

Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A..

Example:

B.part = "abcdefg" matches A.part ="cde"

I cannot put literal in query.. it must be from column data..

It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.

Converting From Many Columns/few Rows To Few Columns/many Rows
So I have a table that looks like:

Code:

char_id1234(...up to 30)
582NULL416
25739NULL12
391NULLNULLNULL

Each char_id is the primary key in another table, each numerical column value is the primary key (skill_id) in a third table, and each data value is the primay key (rank_id) in a fourth table.

I am attempting to join all four tables into a single query, but it seems impossible without converting the table to something like this:

Code:

char_idskil_idrank_id
5812
5834
58416
2517
25239
25412

Count()?
Im trying to count the # of records with the same field (Name) is this the way to do it?

PHP

$Count = mysql_query(" SELECT count(ID), Name FROM REVIEW WHERE Name = '".$Name."'") or die(mysql_error());

Then to display the count all i need to do ia

PHP

echo $Count;

Count()
In my database I have Racing Events and Racing Results.

In the result database each racer that entered the race is giving a place.

I am trying to pull the each total number of races racer with id 1 was in.

Say the Count was 20. I also want to get the total number of people that raced in the same events as him. Say each race had 10 people that count would be 200.

In additional to that I want to count the number of time the racer with id 1 won money.

So say that field was called winnings - Winnings > 0

Add One To Count
im sure this is very simple and that its a stupid moment i am having.

I have a column called db_count and im looking to increment it by one... I have tried this and it wont work, but i cant tell if its the other code i have that wont or this,

is this the way to do it?

UPDATE tbl_makes
SET db_count= db_count +1
WHERE make_name=཈'

Using LIKE Within COUNT?
I am having problems with this query. I have it looking at a table of products sold data - specifically shoes. With shoes there is always the problem of fishing out the data for different sizes.

With the query below I am not sure whether I am able to use LIKE within the COUNT function.

I basically want the query to return a table showing the headings Product which contains all the products (grouped) and COUNTs the number of times each product size comes up.

Here is an example:

+-------------+-----------+-----------+------+-----+-------+
| Product | UK Kids 12 | UK Kids 13 | UK 1 | UK 2 | UK ... |
+-------------+-----------+-----------+------+-----+-------+
| Product 1 | 34 | 52 | 39 | 22 | .... |
+-------------+-----------+-----------+------+-----+-------+

And so on...

Here is the query I have currently however at the moment all it seems to do is return the same number (which happens to be the total of all the sizes - i.e. the total for that shoe model) for each product accross the whole row.

SELECT
SUBSTRING_INDEX(Product,',',1) AS 'Product',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%Kids 12%') AS 'UK Kids 12',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%Kids 13%') AS 'UK Kids 13',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%1%(') AS 'UK 1',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%2%(') AS 'UK 2',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%3%(') AS 'UK 3',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%4%(') AS 'UK 4',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%5%(') AS 'UK 5',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%6%(') AS 'UK 6',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%7%(') AS 'UK 7',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%8%(') AS 'UK 8',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%9%(') AS 'UK 9',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%10%(') AS 'UK 10',
COUNT(SUBSTRING_INDEX(Product,',',-1) LIKE '%11%(') AS 'UK 11'
FROM items_ordered,orders WHERE (Product LIKE 'Heelys%Shoes%') AND

(items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <= &#55614;&#57158;-8-31') GROUP BY Product;

Help With Count(*)
I have a table I'm using for logging purposes with a schema like:

create table results (
user varchar(255)
....
);

Where user is not a unique field and I want to find out how many unique
users there are in the table.

I want to do something like:

select count(count(*)) from results group by user;

But that doesn't work.

Count () Sql How To Use It?
SELECT COUNT(*) FROM Persons WHERE _____________

"________" = I don't know what to enter

I want it count the entries of visitors
whose First Name, Last Name & Email are same

Like this

------------------------------------
First Name Last Name queries
------------------------------------
xyz abc 2
-------------------------------------

First_name
Last_name & email are table columns

Count??
$num = mysql_query("SELECT COUNT(*) FROM `shoutbox`");
echo $num;

it returns "Resource id #28" my table does not have 28 fields but the id goes up to 28 i do not know what is wrong and i am trying to get the number of fields in my table.

Trying To Do A Count
My table is used to keep history of books checked in and out. I need to compile statistics to show number of check in's and check outs each day.

I have been able to generate a report for the check in and checkouts seperately but would like to have them displayed on one report instead of two.

There is one column (status_cd) in the table that records the book status as a 'out' or 'crt'(in the shelving cart).

I merley want to count the number of out's and crt's per day and display them in the same output display. Code:

Count
I have a table full of blog posts. I dont want to create a table just to log hits so i thought i would add a column to that table called 'views'.

I want to create a SQL UPDATE to count++ this, can it be done just int eh sql?

COUNT()
I need to write a SELECT statement that will display the most popular
categories.

This means I need a 'category count' for each of the messages in the
messages table, and I don't know how to do this.

Here is the structure of the 2 tables:

messages table
id, title, message, category, thread, status, date_posted

categories table
id, title, sub_cat

I've tried the following:
select categories.id, categories.title, count(messages.category) as
count from messages, categories group by categories.title;

but 'count' only returns the total count of messages with a category
entry, and I need the number of times each category id is entered in
the messages table.

Any help is greatly appreciated. Also, does anyone have any online
references for learning more complex SQL statements?

Count(*)
I have a table I'm using for logging purposes with a schema like:

create table results (
user varchar(255)
....
);

Where user is not a unique field and I want to find out how many unique
users there are in the table. I want to do something like:

select count(count(*)) from results group by user;

But that doesn't work..

Count(*) And Sum(1)
I am having this interesting SQL problem with mysql Ver 12.22 Distrib
4.0.16, for apple-darwin7.0 (powerpc). For some reason count(*) does
give the right result, when I replace it with sum(1) it works.

mysql> select term, year, count(*) from course where year = 2001 group
by term, year;

+--------+------+--------------------+
| term | year | count(*) |
+--------+------+--------------------+
| Fall | 2001 | 360287970189639680 |
| Spring | 2001 | 360287970189639680 |
+--------+------+--------------------+
2 rows in set (0.00 sec)

mysql> select term, year, sum(1) from course where year = 2001 group
by term, year;
+--------+------+--------+
| term | year | sum(1) |
+--------+------+--------+
| Fall | 2001 | 5 |
| Spring | 2001 | 5 |
+--------+------+--------+
2 rows in set (0.00 sec)

Here is the schema.........

Using COUNT
I have a problem on using the COUNT Function

TableA
+------+-------+----------+
| ID | Status| BatchID |
+------+-------+----------+
| 1 | Pass | 1 |
| 2 | Fail | 1 |
| 3 | Pass | 1 |
| 4 | Pass | 2 |
| 5 | Fail | 3 |
+------+-------+----------+

QUERY Result
+--------+-------------+------------+
|BatchID | Status_Pass | NoOfRecord |
+--------+-------------+------------+
| 1 | 2 | 1 |
| 2 | 1 | 1 |
| 3 | 0 | 1 |
+--------+-------------+------------+

My Query
SELECT A.BatchID, TA.Status_Pass, COUNT(*) AS NoOfRecord
FROM TableA AS A LEFT JOIN
(SELECT BatchID, COUNT(*) AS Status_Pass FROM TableA WHERE Status = 'Pass' GROUP BY BatchID) AS TA
ON (A.BatchID = TA.BatchID)
GROUP BY A.BatchID

Count Rows

with php and mysql: How we can count rows in a table?

with php and mysql:
How we can count rows in a table?