Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Yehuda Epstein
  Where is Yehuda Epstein?
 Monsey
 New York - United States
 Yehuda Epstein
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Calcultions
Thread ID: 268787 Message ID: 268794 # Views: 38 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 5:48:45 PM         
   


Thanks Cetin,
the query:
select product, location, max(amount) as amount ;
from crsIntermediate ;
group by product, location ;
into cursor crsFinal

groups by product and location. I need to group it by product only, since I am only interested in the locations that have the max amount for each product and not the max amount for the combination of product & location.
Example:
Mytable has:
product Amount Quality Location
1 3 1 221
1 5 1 224
1 7 2 221
2 6 2 223
2 5 2 220
2 9 1 221
I would like the cursor crsFinal to display:
product Amount Location
1 10 221
2 9 221

Editing the queries to the following code delivered the requested results.

select product, location, sum(amount) as amount ;
from myTable ;
group by product, location ;
order by product, amount desc ;
into cursor crsIntermediate1 

select product, location, max(amount) as amount ;
from crsIntermediate1 ;
group by product ;
into cursor crsFinal1



Thanks again,
Yehuda.

> > Hi,
> > I have a table with 4 columns Product, Amount, Quality, Location. I need to calculate the amount of each product in each location disregarding the Quality and replace the Amount value with the calaulated amount value in the table the query created, then I need to extarct the records which have the max amount for each product to identify the location, so that the final table will have 3 columns Product, amount (the max amount), Location.
> >
> > Thanks for your help,
> > Yehuda.
>
> Wish you provided some sample data using SampleDataToText.prg here at FAQ section. By calculate i think you mean sum() per location?
>
>
select product, location, sum(amount) as amount ;
> from myTable ;
> group by product, location ;
> into cursor crsIntermediate
> 
> select product, location, max(amount) as amount ;
> from crsIntermediate ;
> group by product, location ;
> into cursor crsFinal
> 
> browse
> 

>
> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> Blog (main)
> Blog (mirror)

ENTIRE THREAD

Calcultions Posted by Yehuda Epstein @ 7/14/2010 5:01:46 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 5:08:38 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 5:48:45 PM
RE: Calcultions Posted by tushar @ 7/14/2010 5:59:14 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 6:10:43 PM
RE: Calcultions Posted by tushar @ 7/14/2010 7:19:10 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 6:24:09 PM
RE: Calcultions Posted by Samir H. @ 7/14/2010 6:48:57 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 7:11:38 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 9:50:36 PM
RE: Calcultions Posted by Samir H. @ 7/15/2010 3:54:29 AM
RE: Calcultions Posted by Cetin Basoz @ 7/15/2010 12:03:52 PM
RE: Calcultions Posted by Samir H. @ 7/15/2010 1:04:35 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 6:49:47 PM
RE: Calcultions Posted by Samir H. @ 7/14/2010 6:58:04 PM