Jump to content

How to use SUMS and COUNTS in WHERE and ORDER BY


Recommended Posts

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

 

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

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

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"

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

 

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

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

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.

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

 

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.