Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Calcultions
Thread ID: 268787 Message ID: 268810 # Views: 26 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 6:48:57 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)

Let me compete with you, Cetin:)
I think this one is the correct one, the one from Yehuda, but it doesn't error in order by.
select product, location, sum(amount) as amount ;
from myTable ;
group by product, location ;
order by location ,product ;
into cursor crsIntermediate1 

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



Regards
Samir

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