How to use SUMS and COUNTS in WHERE and ORDER BY

[wayne]

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

 

[rajivgonsalves]

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

[wayne]

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

[rajivgonsalves]

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"

[wayne]

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

 

[wayne]

Any help on the above please? Cant find any help wth google, lol

[fenway]

The 4th row? The most recent?

[wayne]

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

[fenway]

Really, the fourth one? What does that represent?

[wayne]

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

[fenway]

So you always want the fourth one, for each name?  What if there isn't a fourth?

[wayne]

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.

[fenway]

Well, LIMIT 3,1 will return the 4th row of a recordset.

[wayne]

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

 

 

[fenway]

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...

[wayne]

Thanks, appreciated.

[fenway]

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...