[SOLVED] COUNT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

[SOLVED] COUNT

Post by rsmarsha »

I'm trying to find the number of rows in a table with the same order number.

Would this do it?

Code: Select all

SELECT COUNT(orderno) as amount FROM rma GROUP BY orderno
Then i could display based on where amount=1 or amount=2 ?

Code: Select all

//then a while loop like

$i = 0;
while ($row['amount']=='2')
{
$i++
}
//would put the number with 2 of the same orderno in $i
As i'd need to show all results with 2 of the same orderno and 3 , and 4... and so on. This sound right?
Last edited by rsmarsha on Tue Apr 11, 2006 2:44 am, edited 2 times in total.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

select count(*)
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

So

Code: Select all

SELECT COUNT(*) as amount FROM rma GROUP BY orderno
then the above loop?

Also could i do this?

Code: Select all

SELECT COUNT(*) as amount FROM rma GROUP BY orderno HAVING COUNT(*)=2
As that would give me a value stored in amount which showed the number of rma's having the same order number on 2 occasions?

Not sure if this is what i need. I need to do the following: -

rma orderno

1 456
2 456

means order number 456 has 2 entries in the rma table. I want to find the amount of orders which have 2 rma's associated with them. So i need a query that will count the number of times each order number occurs in the table. I will want to show orders which occur 2, 3, 4 and 5 times. I might have to do this with a new query for each amount or there may be a way to do it all in 1 and find the results. I want to be able to show: -

orders occuring 2 times = amount
orders occuring 3 times = amount

and so on.

Any help much appreciated. :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Re: COUNT

Post by feyd »

rsmarsha wrote:I'm trying to find the number of rows in a table with the same order number.

Would this do it?

Code: Select all

SELECT COUNT(orderno) as amount FROM rma GROUP BY orderno
That would generally do what you want.
rsmarsha wrote:Then i could display based on where amount=1 or amount=2 ?

Code: Select all

//then a while loop like

$i = 0;
while ($row['amount']=='2')
{
$i++
}
//would put the number with 2 of the same orderno in $i
As i'd need to show all results with 2 of the same orderno and 3 , and 4... and so on. This sound right?
You're on the right track.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

Code: Select all

SELECT COUNT(orderno) as amount FROM rma GROUP BY orderno
would group them all by order so : -

orderno count

456 3
457 2
458 2

Then i would want to find the total with a count of 2 and total with 3.

Code: Select all

$i2 = 0;
$i3 = 0;
while ($row = mysql_fetch_assoc($query))
{
if ($row['amount']=='2')
{
$i2++
}
if ($row['amount']=='3')
{
$i3++
}
//would put the number with 2 of the same orderno in $i2
}
that should give $i2 = 2, $i3 = 1.

Does that look about right? Only asking as i'm not on my work pc until tomorrow, but trying to work it out so i can do it when i am, lol. :)

Thanks again and in advance for any tips. :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

roughly, it's fine.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

Thanks, i made a couple of errors with the code, but have it working now. :)

Thanks again for the help.
Post Reply