How to sum the table records?

[Alex Murphy]

Hi all,

I using the "list" function to extract the sales text delimited file
and insert into the table. How to find out the best sales item and
worst sales item?

item | qty
---------------------------------------
11111 | 2
22222 | 1
33333 | 1
44444 | 1
55555 | 3
22222 | 2
11111 | 1
55555 | 4
22222 | 2
66666 | 1

Thanks

Alex Murphy

[Rik]

Re: How to sum the table records?

Alex Murphy wrote:[color=blue]
> Hi all,
>
> I using the "list" function to extract the sales text delimited file
> and insert into the table. How to find out the best sales item and
> worst sales item?[/color]

Summing is easy:

SELECT `item`, SUM(`qty`) AS 'total_sold' FROM table_name GROUP BY `item`

Extracting best and worst in one query in one go is a little bit trickier.
I'm sure it's possible in one query, but I'm not familiar enough with JOINS
or subqueries to figure that one out.

That's more of a database question, which database are you using?

Grtz,
--
Rik Wasmus

[Alex Murphy]

Re: How to sum the table records?

The database is using text file to store the data. I using "list" and
"split" function to split the "|" delimiter.
This text file will upload to the server from the shop everydays.

[Rik]

Re: How to sum the table records?

Alex Murphy wrote:[color=blue]
> The database is using text file to store the data. I using "list" and
> "split" function to split the "|" delimiter.
> This text file will upload to the server from the shop everydays.[/color]

OK, you don't use a database, only a textfile. Check:
http://nl3.php.net/manual/en/function.fgetcsv.php and the like
I'd say this should be database work, but hey, arrays work too, only with a
little bit more hassle.
It would be a lot of help if you would have mentioned HOW you create the
arrays, keys etc.

If you've made an array containing: index=>array(it em, qty)

/* sum the different rows on item */
foreach($array as $row){
$totals[$row['item']] += $row['qty']
}

/* get max and min value */
$max = max($totals);
$min = min($totals);

/* We need an array_search_al l, mark meves posted one on php.net */

function array_search_al l($needle,$hays tack){
return array_keys(arra y_filter($hayst ack, create_function ('$s','return $s
== \''.addslashes( $needle).'\';') ));
}

/* get id's where the qty is max or min */
$max_items = array_search_al l($max, $totals);
$min_items = array_search_al l($in, $totals);

Is that what you want, or is my lack of detailed knowledge of the english
language causing me to do something completely different?

Grtz,
--
Rik Wasmus

[Alex Murphy]

Re: How to sum the table records?

Oh.....Thanks Rik....Thanks your help.


Alex Murphy

[Tim Van Wassenhove]

Re: How to sum the table records?

On 2006-05-07, Alex Murphy <murphychan168@ gmail.com> wrote:[color=blue]
> The database is using text file to store the data. I using "list" and
> "split" function to split the "|" delimiter.
> This text file will upload to the server from the shop everydays.[/color]

Well, then it's about time to start looking at the manual of your SQL
DBMS. You'll find a section that allows you to use 'normalize' the data
and you'll find a section that allows you to import CSV (or other delimiter
separated value) files.

--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be >