Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Yehuda Epstein
  Where is Yehuda Epstein?
 Monsey
 New York - United States
 Yehuda Epstein
 Tags
Subject: RE: Calcultions
Thread ID: 268787 Message ID: 268805 # Views: 31 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 6:24:09 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.
>

I see. It is good to have a sample:) Correct SQL would be:

Select Product, Location, Sum(Amount) As Amount ;
  from myTable ;
  group By Product, Location ;
  into Cursor crsIntermediate

Select Product, Location, Amount ;
  FROM crsIntermediate t1 ;
  WHERE Amount = ;
  (Select Max(Amount) From crsIntermediate t2 Where t2.Product = t1.Product) ;
  INTO Cursor crsFinal


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