Posted by John MacLeod on 11/16/05 18:53

"Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote in message
news:dlf441$fp8$1@news.onet.pl...
>
>
> Are you sure that all orders of "Apple Brown Betty" have exactly same
> values in "product", "title" and "price" fields? If they do not (or they
> are same, but CAN differ because there's no mechanism to prevent
> this), then the problem is a bit more complex.
>

The only field that may differ is the "price" because someone may have used
a coupon...but to be honest I don't need the price in there anyway so I have
taken it out.

What I could use here, however, is the "cost_price" (what we pay our
supplier) but that is stored in a different table "store_inventory" and I'm
not sure how to retrieve that information from that table (which why I
haven't even tried to this point) and link it to products listed from
"store_order_inv" where "order_from_supplier" = 1? Unlike the "price" value,
I know that the "cost_price" value will always be the same. Also, if I am
able to retrieve the "cost_price" it would have to be a totaled...
"cost_price" x "sum_quantity". And finally it would be useful to have a
total of all the values in the "cost_price" fields to give us a total cost
of our order from our supplier. This is WAY too much for me to figure out on
my own...but hopefully you (or someone else) can lead me step by step...

....by the way, doing this isn't 100% necessary for us...we can get along
without it...the real reason I want to do all of this is to see if I can
(with help, obviously) figure it out, simply because SQL intrigues me and I
wish I had more time on my hands to learn more about it...I applaud the
people who do have a good knowledge of SQL as it opens up so many
possibilities that us part-time "hackers" only dream of...OK, enough butt
kissing for now...


>
> SELECT product, title, price, SUM( quantity ) AS sum_quantity
> FROM store_order_inv
> WHERE order_from_supplier = 1
> GROUP BY product, title, price
> ORDER BY product ASC
>

I took out the "price" field from the above and have got this
working...thank you once again...

Sincerely,
John

[Back to original message]