Posted by Brian on 06/02/05 18:41

Hi Joseph

Thanks for the reply, I have to admit I'm a little lost on what you have
said, I have had a look round the net to try and understand it more,
but now I'm ever more lost.

The table is as follows

id INT AUTO_INCREMENT PRIMARY KEY,
date varchar(50),
orderno varchar(50),
store varchar(100),
storename varchar(100),
dnote varchar(50),
status varchar(20),
product varchar(200),
description varchar(200),
ord varchar(50),
del varchar(50),
brand varchar(50),
timestamp varchar(20)


what I need to do is say select from the table all record where timestamp is
between X and Y, and
return 4 variables added up

$notrecognisedcount = total up 'ord' where status equals 'n'
$discontinuedcount = total up 'ord' where status equals 'u'
$validorderscount = total up 'ord' where status not equal 'n' or 'u'
$delivered = total up 'del' where status not equal 'n' or
'u'


Does that make sense?

Brian









"Joseph Melnick" <jmelnick@jphp.com> wrote in message
news:-MmdneQo94opMgHfRVn-sg@rogers.com...
> Hello Brian,
>
> If you want to add the values in a number of columns:
>
> create a query like this
> $ch == 'n'
> $ch == 'u'
> ($ch != 'n') && ($ch != 'u')
>
>
> select sum(IF(ch='n',1,0)) notrecognisedcount,
> sum(IF(ch='u',1,0)) discontinuedcount,
> (count(1)-sum(IF(ch='n',1,0))-sum(IF(ch='y',1,0)))
> validorderscount
> from table
>
> This type of query may give you what you are looking for directly without
> having to loop through
> each record and go though lots of external logic to get your info from the
> database.
>
> If I understood the tables that you are working with then it would be an
> easy matter of using writing a better query.
>
> I hope that this helps.
>
> Joseph Melnick
> JM Web Consultants
> http://www.jphp.com
>
>
>
>
> Joseph Melnick
>
>
> "Brian" <not@given.com> wrote in message
> news:j7_me.2891$cN2.920@newsfe4-gui.ntli.net...
>> Hi all
>>
>>
>>
>> I'm running a query in a PHP script. At the moment I have having to run a
>> while ($myrow = mysql_fetch_array($result)) {...
>>
>>
>>
>> As it loops though is has to add up a number of columns, is there a
>> quicker sql statement then doing this the following
>>
>>
>>
>> if ($ch == 'n') {
>>
>> $notrecognisedcount = $myrow[ord];
>>
>> }
>>
>> if ($ch == 'u') {
>>
>> $discontinuedcount = $myrow[ord];
>>
>> }
>>
>> if ( ($ch != 'n') && ($ch != 'u') ) {
>>
>> $validorderscount = $myrow[ord];
>>
>> $delivered = $myrow[del];
>>
>> }
>>
>>
>>
>>
>>
>>
>>
>> Brian
>>
>>
>>
>>
>
>

[Back to original message]