Page 1 of 1

[SOLVED] COUNT

Posted: Mon Apr 10, 2006 5:44 am
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?

Posted: Mon Apr 10, 2006 5:54 am
by s.dot
select count(*)

Posted: Mon Apr 10, 2006 5:56 am
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. :)

Re: COUNT

Posted: Mon Apr 10, 2006 8:46 am
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.

Posted: Mon Apr 10, 2006 9:57 am
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. :)

Posted: Mon Apr 10, 2006 10:03 am
by feyd
roughly, it's fine.

Posted: Tue Apr 11, 2006 2:44 am
by rsmarsha
Thanks, i made a couple of errors with the code, but have it working now. :)

Thanks again for the help.