Re: Sort on group totals
Ricardo Aráoz wrote: > In SQLServer you could do : > select * > from ( > Select Foo, bar from MyJunk; > UNION; > Select Foo, bar from YourJunk; > ) MyStuff > order by bar, Foo > > But you can not do that in VFP 6. Is it possible in VFP 9? > To my great surprise, yes it is. Neat. Thanks! ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Vince Teachout wrote: > MB Software Solutions wrote: >> Chester Friesen wrote: >> >>> BTW, what does the 4 do after the Order By, also the 1 after the Desc? >>> > > What Mike said. I usually use the column names instead of the column > numbers, but there are a few places where you have no choice but to use > the numbers - in a UNION query, for example: > > Select Foo, bar from MyJunk; > UNION; > Select Foo, bar from YourJunk; > ORDER BY Bar, Foo&& Blows up > > Select Foo, bar from MyJunk; > UNION; > Select Foo, bar from YourJunk; > ORDER BY 2,1&& OK > In SQLServer you could do : select * from ( Select Foo, bar from MyJunk; UNION; Select Foo, bar from YourJunk; ) MyStuff order by bar, Foo But you can not do that in VFP 6. Is it possible in VFP 9? ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Chester Friesen wrote: > SELECT riderid, ; > SUM(nscore) AS totscore ; > FROM Foobar; > GROUP BY riderid ; > ORDER BY totscore DESC ; > INTO CURSOR totcurs nofilter > > SELECT * ; > FROM Foobar; > INNER JOIN totcurs ; > ON Foobar.riderid = totcurs.riderid ; > ORDER BY totscore DESC,1 > Just to nitpick-- you don't need the Order by Clause in the first query, because the 2nd query is your final output, and that 2nd Order by will override anything previous. :-) Have fun! ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
MB Software Solutions wrote: > Chester Friesen wrote: > >> BTW, what does the 4 do after the Order By, also the 1 after the Desc? >> What Mike said. I usually use the column names instead of the column numbers, but there are a few places where you have no choice but to use the numbers - in a UNION query, for example: Select Foo, bar from MyJunk; UNION; Select Foo, bar from YourJunk; ORDER BY Bar, Foo&& Blows up Select Foo, bar from MyJunk; UNION; Select Foo, bar from YourJunk; ORDER BY 2,1&& OK ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Chester Friesen wrote: > BTW, what does the 4 do after the Order By, also the 1 after the Desc? > It allows you to specify the column to be used by column reference number, as in the 4th column from your SELECT statement, as opposed to naming the field specifically. But be careful: if you come back to this query later and add something before the last field listed in the SELECT statement, you may not be referring to the same field anymore. For example: select cName, dDOB, cEyeColor, cHairColor, sum(iTestScore) from Dating group by 1,2,3,4 order by 4 ...and then you go and add field like so: select cName, dDOB, cEyeColor, cHairLength, cHairColor, sum(iTestScore) from Dating group by 1,2,3,4,5 order by 4 ...you're going to get a different sort order, with the first query sorting on cHairColor, whereas the second query sorts on cHairLength. (That is of course unless cHairColor = 'BLONDE'.hahaha) -- Michael J. Babcock, MCP MB Software Solutions, LLC http://mbsoftwaresolutions.com http://fabmate.com "Work smarter, not harder, with MBSS custom software solutions!" ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Vince Teachout wrote: > Actually, if you have VFP9, you can do it with one query: > > SELECT A.Rider, A.dDate, A.nScore, ; > (Select SUM(nscore) FROM foobar B WHERE b.rider = a.rider) ; > FROM foobar A; > ORDER BY 4 desc,1 > I have VFP9, but I started this project before I got it. -- Regards, Chester Friesen Friesen Computer Company Willows, CA ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Vince Teachout wrote: > Well, Here's one way: > > SELECT rider, SUM(nscore) as totscore FROM foobar GROUP BY rider INTO > CURSOR totcurs nofilter > > SELECT Foobar.Rider, Foobar.Ddate, Foobar.Nscore, totcurs.totscore ; > FROM foobar INNER JOIN totcurs ON foobar.rider = totcurs.rider ; > ORDER BY 4 desc,1 > Hey Vince, you got me on the right track, thanks a million! Here's how I did it, I haven't got it into a report yet because I need some more inner joins to get the rider names into it, but I think it will work. SELECT riderid, ; SUM(nscore) AS totscore ; FROM Foobar; GROUP BY riderid ; ORDER BY totscore DESC ; INTO CURSOR totcurs nofilter SELECT * ; FROM Foobar; INNER JOIN totcurs ; ON Foobar.riderid = totcurs.riderid ; ORDER BY totscore DESC,1 BTW, what does the 4 do after the Order By, also the 1 after the Desc? -- Regards, Chester Friesen Friesen Computer Company Willows, CA ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Chester Friesen wrote: > I need all the > records in the table to show in the report sorted by the order that the > above came up with. i.e., sorted by the Names, but in the order of the > Totals. I hope I'm making sense! Actually the example below should be > descending instead of ascending, but that's an easy part. Actually, if you have VFP9, you can do it with one query: SELECT A.Rider, A.dDate, A.nScore, ; (Select SUM(nscore) FROM foobar B WHERE b.rider = a.rider) ; FROM foobar A; ORDER BY 4 desc,1 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Chester Friesen wrote: > I need all the > records in the table to show in the report sorted by the order that the > above came up with. i.e., sorted by the Names, but in the order of the > Totals. I hope I'm making sense! Actually the example below should be > descending instead of ascending, but that's an easy part. > > Main Heading > Name1 > 01/01/07 50 > 01/01/07 25 > Total-> 75 > Name2 > 01/01/07 40 > 01/01/07 44 > Total-> 84 > Name3 > 01/01/07 45 > 01/01/07 44 > Total-> 89 > O.I.C. Well, Here's one way: SELECT rider, SUM(nscore) as totscore FROM foobar GROUP BY rider INTO CURSOR totcurs nofilter SELECT Foobar.Rider, Foobar.Ddate, Foobar.Nscore, totcurs.totscore ; FROM foobar INNER JOIN totcurs ON foobar.rider = totcurs.rider ; ORDER BY 4 desc,1 Output: Name3 20070101 44 89 Name3 20070101 45 89 Name2 20070101 40 84 Name2 20070101 44 84 Name1 20070101 50 75 Name1 20070101 25 75 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Vince Teachout wrote: > I must not be understanding the problem. This works fine for me in vfp7: > SELECT stylename, sum(units) as sumall FROM gmoodet GROUP BY stylename > ORDER BY sumall > I'm learning here. ;-) That's close, that gives me a one record for each rider with a "sumall" field showing the total. I need all the records in the table to show in the report sorted by the order that the above came up with. i.e., sorted by the Names, but in the order of the Totals. I hope I'm making sense! Actually the example below should be descending instead of ascending, but that's an easy part. Main Heading Name1 01/01/07 50 01/01/07 25 Total-> 75 Name2 01/01/07 40 01/01/07 44 Total-> 84 Name3 01/01/07 45 01/01/07 44 Total-> 89 -- Regards, Chester Friesen Friesen Computer Company Willows, CA ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Sort on group totals
Vince Teachout <> wrote: > Chester Friesen wrote: >> I have an app in VFP7 that I need to sort on group totals. Is that a >> job for VFP9? Forget it for VFP7? >> Now, the report looks nice, but needs the totals sorted on each >> group. I have tried a 2 pass routine, but it's not really working >> right. I don't know if this is enough info to give me some ideas or >> not. >> > > I must not be understanding the problem. This works fine for me in > vfp7: > SELECT stylename, sum(units) as sumall FROM gmoodet GROUP BY > stylename ORDER BY sumall > > Or, sometimes when I do have problems sorting on a column becuase "no > such column", I can use positional sorting: > SELECT stylename, sum(units) as sumall FROM gmoodet GROUP BY > stylename ORDER BY 2 Unless of course you have details in your report and you want to have the HIGHEST subtotal on top, page 1 and then the next as the data rolls. You need a two table solution with a relationship between them if I have described the issue properly. HTH Stephen Russell DBA / .Net Developer Memphis TN 38115 901.246-0159 "A good way to judge people is by observing how they treat those who can do them absolutely no good." ---Unknown http://spaces.msn.com/members/srussell/ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.17.17/661 - Release Date: 1/30/2007 11:30 PM ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Chester Friesen wrote: > I have an app in VFP7 that I need to sort on group totals. Is that a job > for VFP9? Forget it for VFP7? > Now, the report looks nice, but needs the totals sorted on each group. I > have tried a 2 pass routine, but it's not really working right. I don't > know if this is enough info to give me some ideas or not. > I must not be understanding the problem. This works fine for me in vfp7: SELECT stylename, sum(units) as sumall FROM gmoodet GROUP BY stylename ORDER BY sumall Or, sometimes when I do have problems sorting on a column becuase "no such column", I can use positional sorting: SELECT stylename, sum(units) as sumall FROM gmoodet GROUP BY stylename ORDER BY 2 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Chester Friesen wrote: > I have an app in VFP7 that I need to sort on group totals. Is that a job > for VFP9? Forget it for VFP7? > Now, the report looks nice, but needs the totals sorted on each group. I > have tried a 2 pass routine, but it's not really working right. I don't > know if this is enough info to give me some ideas or not. > > Regards, > Chester Friesen > > Hi, don't know if this is what you need : create cursor MyCursor (TotFld N(2), Num N(12,2)) Insert some random values here select TotFld, sum(Num) as MyTotal ; into cursor MyResult ; from Mycursor ; group by TotFld ; order by 1 and voilá, MyResult is ordered by TotFld and you have totals. Hope it helps. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
MB Software Solutions wrote: > You should be able to do it with VFP5 and up! Just create a column > called iSort and then write logic to populate accordingly, then order by > that column. (Or perhaps I'm missing your point?) > I'll think that through a little, I was totalling each riders data to a separate table with a total field, using that table to scan through the riders to make another sorted table or cursor. Couldn't get anything reliable, though. Regards, Chester Friesen Friesen Computer Company ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Sort on group totals
Chester Friesen wrote: > I have an app in VFP7 that I need to sort on group totals. Is that a job > for VFP9? Forget it for VFP7? > Now, the report looks nice, but needs the totals sorted on each group. I > have tried a 2 pass routine, but it's not really working right. I don't > know if this is enough info to give me some ideas or not. > You should be able to do it with VFP5 and up! Just create a column called iSort and then write logic to populate accordingly, then order by that column. (Or perhaps I'm missing your point?) -- Michael J. Babcock, MCP MB Software Solutions, LLC http://mbsoftwaresolutions.com http://fabmate.com "Work smarter, not harder, with MBSS custom software solutions!" ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
