wayne Posted December 27, 2007 Share Posted December 27, 2007 Hi I have a query as below where most of the columns returned are derived so to speak and not actuals columns, the queston is if I want to use these in WHERE or ORDER BY how do I do it, seems to return null rows for me. My original statement without the where clause was select trainer, course, count(poscalc) as runs, sum(poscalc) as wins, 100/count(poscalc)/sum(poscalc) as 'win%', sum(backlsp) as 'back lsp (£)', sum(laylsp) as 'lay lsp(£)', avg(decodds) as 'ave odds' from flatresults, flatraces where racenumbera = racenumber and 50>= (select count(poscalc) as runs from flatresults) group by trainer, course I then added a where clause as only want records as below but doe snt work, im obviouslt doing somethign majorly wrong, I also then want to put an order by on the end of the statement for the same value used for the WHERE, i.e count(poscalc) as runs select trainer, course, count(poscalc) as runs, sum(poscalc) as wins, 100/count(poscalc)/sum(poscalc) as 'win%', sum(backlsp) as 'back lsp (£)', sum(laylsp) as 'lay lsp(£)', avg(decodds) as 'ave odds' from flatresults, flatraces where racenumbera = racenumber and 50>= (select count(poscalc) as runs from flatresults) group by trainer, course Feel free to also comment on the statement in general as new to sql so recommendations for performance of the statement appreciated too. Any help greatly appreciated. Wayne Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/ Share on other sites More sharing options...
rajivgonsalves Posted December 27, 2007 Share Posted December 27, 2007 your query should be select trainer, course, count(poscalc) as runs, sum(poscalc) as wins, 100/count(poscalc)/sum(poscalc) as 'win%', sum(backlsp) as 'back lsp (£)', sum(laylsp) as 'lay lsp(£)', avg(decodds) as 'ave odds' from flatresults, flatraces where racenumbera = racenumber group by trainer, course having count(poscalc) >= 50 hope its helpful Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-424041 Share on other sites More sharing options...
wayne Posted December 27, 2007 Author Share Posted December 27, 2007 Thanks How do you use multiple having clauses, i.e the below is wrong. select trainer, course, count(poscalc) as Runs, sum(poscalc) as Wins, 100/count(poscalc)*sum(poscalc) as 'Win%', sum(backlsp) as 'Back lsp (£)', sum(laylsp) as 'Lay lsp(£)', avg(decodds) as 'Ave Odds' from flatresults, flatraces where racenumbera = racenumber group by trainer, course having count(poscalc) >= 50, sum(baclsp) >=50 order by Runs desc Wayne Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-424047 Share on other sites More sharing options...
rajivgonsalves Posted December 27, 2007 Share Posted December 27, 2007 this should work select trainer, course, count(poscalc) as Runs, sum(poscalc) as Wins, 100/count(poscalc)*sum(poscalc) as 'Win%', sum(backlsp) as 'Back lsp (£)', sum(laylsp) as 'Lay lsp(£)', avg(decodds) as 'Ave Odds' from flatresults, flatraces where racenumbera = racenumber group by trainer, course having count(poscalc) >= 50 and sum(baclsp) >=50 order by Runs desc instead of the comma use a "and" Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-424049 Share on other sites More sharing options...
wayne Posted December 28, 2007 Author Share Posted December 28, 2007 Thanks Another request if I may If still using the below statment which returns multiple rows for 'name", how do i tell it to only return the row which is the 4th row found for the same name? note, if off any help each row is in date order as well select trainer, course, name, count(poscalc) as runs, sum(poscalc) as wins, 100/count(poscalc)/sum(poscalc) as 'win%', sum(backlsp) as 'back lsp (£)', sum(laylsp) as 'lay lsp(£)', avg(decodds) as 'ave odds' from flatresults, flatraces where racenumbera = racenumber group by trainer Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-424808 Share on other sites More sharing options...
wayne Posted December 30, 2007 Author Share Posted December 30, 2007 Any help on the above please? Cant find any help wth google, lol Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-425929 Share on other sites More sharing options...
fenway Posted December 31, 2007 Share Posted December 31, 2007 The 4th row? The most recent? Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-426918 Share on other sites More sharing options...
wayne Posted January 1, 2008 Author Share Posted January 1, 2008 Hi Nope, may have unlimited number of entries for name is date order, need to identify which one was the 4th for each name. I can do this via a query or if need be add a column to table and run an update script if its easier to give a number incrementing by 1 every time that name exists. Obviously increase by 1 by date asc Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-427364 Share on other sites More sharing options...
fenway Posted January 2, 2008 Share Posted January 2, 2008 Really, the fourth one? What does that represent? Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-428132 Share on other sites More sharing options...
wayne Posted January 2, 2008 Author Share Posted January 2, 2008 Sorry misunderstood your previous question. The 4th one does not represent anything, its just the 4th row of data found for a unique name where multiple rows exist for that name. The row I want to find will change for different queries i write, may be 3rd, 6th etc in a different query. I guess what I need is a fucntion like RANK but for mysql i.e i have NUMBER, NAME 1, JOHN 2, PETER 3, KARL 4, PETER 5, JOHN 6, JOHN 7, PETER 8, KARL 9, JOHN What i want to do is to find the 4th row of say JOHN Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-428253 Share on other sites More sharing options...
fenway Posted January 2, 2008 Share Posted January 2, 2008 So you always want the fourth one, for each name? What if there isn't a fourth? Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-428257 Share on other sites More sharing options...
wayne Posted January 2, 2008 Author Share Posted January 2, 2008 yep the 4th for each name, if isnt one then just does not obviosly return a row. Here is the practical use for it in this scenario The 'name' is a horse and I want to find the 4th run for each horse to see how they performed on their 4th start. Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-428466 Share on other sites More sharing options...
fenway Posted January 2, 2008 Share Posted January 2, 2008 Well, LIMIT 3,1 will return the 4th row of a recordset. Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-428745 Share on other sites More sharing options...
wayne Posted January 3, 2008 Author Share Posted January 3, 2008 Thanks How do i use that for a group by or order by clause, tried multiple tests and can only get it to return the first row for a set amount of record sets i.e tried bekow which only returns 1st row of data for first 4 record sets select distinct (name), racedate from jumpresults, jumpraces where racenumber = racenumbera group by name order by name limit 0,4 tried below which returns 1st row for 4th record set select distinct (name), racedate from jumpresults, jumpraces where racenumber = racenumbera group by name order by name limit 3, 1 tried below which still returns me 1st row for 4th record set select distinct (name), racedate from jumpresults, jumpraces where racenumber = racenumbera group by name limit 3,1 tried below which returns me first row for 4 sets startign from the first select distinct (name), racedate from jumpresults, jumpraces where racenumber = racenumbera group by name limit 0,4 I want to return the 4th row for every single record set exists where a 4th row exists ? Im obviously being stupid and not understanding. Google didnt help me either, lol Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-429145 Share on other sites More sharing options...
fenway Posted January 3, 2008 Share Posted January 3, 2008 It's going to be tricky to do this for every "name"... because each would require its own LIMIT & OFFSET. Hmm... let me think about that for a bit... Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-429281 Share on other sites More sharing options...
wayne Posted January 3, 2008 Author Share Posted January 3, 2008 Thanks, appreciated. Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-429494 Share on other sites More sharing options...
fenway Posted January 5, 2008 Share Posted January 5, 2008 I suppose you could run a query using a bunch of user variables to find out how many records are present for each user, then "mark" the fourth, then restrict with a HAVING clause, and then join *that* table in... Quote Link to comment https://forums.phpfreaks.com/topic/83347-how-to-use-sums-and-counts-in-where-and-order-by/#findComment-431164 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.