Help calculating columns using SQL in VFP9 3

[Luiz Eduh]

Hello,

Here's what I would like to accomplish but i'm not sure if is even possible.
Here's a the data;
Table1

 item  ordqty  shipqty  price 
 1234    25      23      5.99

SELECT table1.item, SUM(table1.ordqty - table1.shipqty)as total_result......

I would like to multiply the result of the above with the price field, in other words whatever the total of table1.ordqty - table1.shipqty is, multiply the result with the price field and put it in new column, this will give me a total of loss for that item. In the above table a customer order 25 items but only 23 shipped, therefore 2 were shorted (2*5.99=11.98 of loss in this sale. Can this be done in SQL? If so your help will be greatly appreciated.

Thanks in advanced.

 

[TamarGranor]

I'm assuming you have only one record for each item. If that's correct, this should work:

SELECT item, (ordqty - shipqty) AS QtyUnsold, (ordqty - shipqty)*Price as total_loss
    FROM Table1

Tamar

 

[Olaf Doschke]

Why not simply SUM(price*(ordqty-shipqty))? If you can sum ordqty-shipqty what is so estonishing or inconceivably to also be able to SUM any expression you like?

Bye, Olaf.

 

[mjcmkrsr]

Even more simple

SELECT item, ordqty, shipqty, price, ((ordqty - shipqty)*Price) as Loss FROM Table1

m

 

[Mike Lewis]

There seems to be a bit of confusion here. Are we talking about finding the profit per item - where each record in the table corresponds to one item? If so, then Tamar's or M's solution would work.

Or are we talking about aggregating the items in some way? In that case, you would need the SUM() function, but that only makes sense if there is a GROUP BY clause.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, SUM also sums all records - as one group, if you don't add a GROUP BY clause. There's nothing wrong about using SUM, if you understand this sums multiple records. Calculating anything for each single record, you only write whatever mathematical expression you want to compute, that's true. SUM like any of the other aggragation functions COUNT, AVG, STDEV, many more depending on database and also MIN/MAX, though they only pick one minimum or maximum value, are acting per group of records, or all records, when you don't specify a GROUP BY criterion. Despite the mentioned nature of MIN and MAX any such function acts on multiple records unless a group only has one record. And in the sense of looking at a grpup of records also MIN/MAX do exactly that, they only pick the min or max value of all values of that group, so they also aggregate data.

Bye, Olaf.

 

[Luiz Eduh]

Thank you,

I really appreciate the help.