470,862 Members | 1,852 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,862 developers. It's quick & easy.

How to sum the table records?

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

May 7 '06 #1
5 4417
Rik
Alex Murphy wrote:
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?


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
May 7 '06 #2
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.

May 7 '06 #3
Rik
Alex Murphy wrote:
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.


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(item, 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_all, mark meves posted one on php.net */

function array_search_all($needle,$haystack){
return array_keys(array_filter($haystack, create_function('$s','return $s
== \''.addslashes($needle).'\';')));
}

/* get id's where the qty is max or min */
$max_items = array_search_all($max, $totals);
$min_items = array_search_all($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
May 7 '06 #4
Oh.....Thanks Rik....Thanks your help.
Alex Murphy

May 8 '06 #5
On 2006-05-07, Alex Murphy <mu***********@gmail.com> wrote:
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.


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>
May 10 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

36 posts views Thread by toedipper | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.