Select Sql Sum group by

[newtofoxpro]

CREATE CURSOR CurTrn (TrnTpe c(10), TrnProd c(20), TrnQty n(15,2), TrnRate n(15,2), TrnRPer c(5), TrnAmt n(15,2))
INSERT INTO CurTrn VALUES ("SALE","PENCIL",10,4,'Pcs',50)
INSERT INTO CurTrn VALUES ("SALE","PENCIL",10,5,'Pcs',60)
INSERT INTO CurTrn VALUES ("FREE","PENCIL",10,0,'   ',0)
SELECT TrnProd, SUM(TrnQty),TrnRate,TrnRPer,SUM(TrnAmt) FROM CurTrn GROUP BY 1

Result looking for

Pencil,30,5,Pcs,110.00

Anybody knows solution ?

 

[Mike Lewis]

What you want doesn't really make sense, if you think about it. You are grouping on the product, and asking for the totals for the quantity and amount. So far, so good. But you are also asking for the rate and the unit size (TrnRPer) for the group. The trouble is that different records within the group have different rates and unit sizes. How would you expect VFP to know which one you want?

In general, the rule for using a GROUP BY clause is that every expression in the expression list (immediately after the word SELECT) must be either a field that you are grouping on or an aggregate expression. An aggregate expression is one like SUM() or COUNT(). You have two expressions (TrnRate and TrnPer) which don't meet those criteria.

In fact, your SELECT would work in VFP 8 and earlier, which didn't enforce the above rule. And you can make it work in VFP 9 by issuing SET ENGINEBEHAVIOR 80. But doing so is bad practice, because it could (and probably will) lead to unexpected values in the result set.

Try removing TrnRate and TrnPer and see how well that works.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[newtofoxpro]

Total Pencils sold
Qty=30
Last Rate = 5
Unit Maintained with = Pcs
Amount=110.00

 

[Mike Lewis]

So now you are asking for last rate. You didn't mention that before.

So how you do you define "last rate"? Is it the highest rate? If so, add MAX(TrnRate) to your expression list. That will meet the GROUP BY criteria, as it an aggregate expression.

But I won't try and second-guess which "Unit maintained with" you want. I'll wait until you have explained the problem clearly and completely.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[newtofoxpro]

Rate per Pcs

Please try this

SELECT TrnProd, SUM(TrnQty),TrnRate,TrnRPer,SUM(TrnAmt) FROM CurTrn WHERE TrnTpe="SALE" GROUP BY 1

Query result take place with last record value of TrnRate,TrnRPer

 

[newtofoxpro]

verbose

CREATE CURSOR CurTrn (TrnTpe c(10), TrnProd c(20), TrnQty n(15,2), TrnRate n(15,2), TrnRPer c(5), TrnAmt n(15,2))
INSERT INTO CurTrn VALUES ("SALE","PENCIL",10,4,'Pcs',50)
INSERT INTO CurTrn VALUES ("SALE","PENCIL",10,5,'Pcs',60)
INSERT INTO CurTrn VALUES ("FREE","PENCIL",10,0,'   ',0)
CREATE CURSOR Query (TrnProd c(20), TrnQty n(15,2), TrnRate n(15,2), TrnRPer c(5), TrnAmt n(15,2))
SELECT CurTrn
GO TOP
DO WHILE !EOF()
   VarTrnProd=TrnProd
   STORE 0 TO SumTrnQty,VarTrnRate,SumTrnAmt
   STORE SPACE(5) TO VarTrnRPer
   SCAN WHILE TrnProd=VarTrnProd
      SumTrnQty=SumTrnQty+TrnQty
      VarTrnRate=IIF(TrnRate=0,VarTrnRate,TrnRate)
      VarTrnRPer=IIF(EMPTY(TrnRPer),VarTrnRPer,TrnRPer)
      SumTrnAmt=SumTrnAmt+TrnAmt
   ENDSCAN
   INSERT INTO Query VALUES (VarTrnProd,SumTrnQty,VarTrnRate,VarTrnRPer,SumTrnAmt)
ENDDO
SELECT Query
BROWSE

 

[Mike Lewis]

Look, I don't want to discourage you from asking questions. Like most of us here, I do try hard to understand what people are posting, and do my best to give helpful answers.

But, really, you are making life very difficult for yourself.

You start by showing us a SELECT statement, and asking if "anyone knows solution" - without stating what problem you are seeing. I replied by suggesting what might be wrong with the code - but only guessing the problem.

You then threw in some more figures, still without any sort of explanation of what the problem is or what you were trying to achieve. I made another guess about what you wanted to know, but that didn't seem to answer your question either.

Then you asked me to try running a different version of the SELECT. And finally you posted some code that didn't include a SELECT at all, but showed - and again, I'm guessing - another way of achieving the desired result.

You could save us all a lot of time if you simply take a deep breath, step back from the details, and explain in plain simple English what you are trying to achieve, and what problem you are seeing. Simply posting code or projected results without any explanation is not at all helpful.

I'm sorry if this sounds slightly critical. I am trying to help solve the problem, but you must provide some help as well.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Redundancy.

Let's talk about it.

If there is a unit, a product is maintinesd with, this is a product attribute, not a transaction attribute. What you put into the TrnRPer field is redundant to the info about the product PENCIL, despite the fact you have an empty string in case of FREE instead of SALE.

The table you have there rather is a sheet of report data than it is normilzed database data. I'll stop aout this topic at this point.


In your query you group by the first column, SALE vs FREE and not by product, you can get any unit, if you have sales of pencils and anything else you sell by oz or litres or ounces or grams, you'll not get any good result.

Your nested loop "groups by" TrnProd, the product. That is the second column, not the first.

You have to pay, if you don't want to pay money you pay time. Anyway, don't be cheap with attention.

Bye, Olaf.