+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: sub query problems SOLVED (/showthread.php?tid=29972)
Pages:
1
2
sub query problems SOLVED - El Forum - 04-27-2010
[eluser]srpurdy[/eluser]
Hi,
I have a query in sql that is grabbing a bunch of various information, some of these fields are doing calculations using sum()
I'd like to be able to limit the sum results. So that for example instead of getting the total from all records that fit the query criteria, to only get total of an X amount of records.
So for a quick example of what I'd like it to do is say I have 5 rows with
10,10,10,10,10 -> result would be 50. Id like to limit that to say 3 rows 10,10,10 -> result is 30. however I don't want to limit the rows outputed, only limit the total
I figured I could do this using a sub query but I can't seem to figure out the right syntax using the query I already have constructed below. Pretty much every single one of those sum() needs to have that limit factor. Any help on direction on how this could be done would be appreciated.
Code:
function get_champ()
{
$show_champ = $this->db
->where('ilr_champ.season_id', $this->uri->segment(4))
->where('ilr_season.season_id', $this->uri->segment(4))
->where('ilr_event_result.iracing_cust_id = ilr_champ.iracing_cust_id')
->where('ilr_event_result.schedule_id = ilr_champ.schedule_id')
->where('ilr_champ.schedule_id = ilr_events.schedule_id')
->where('ilr_events.track_id = ilr_tracks.track_id')
->select('
ilr_event_result.iracing_cust_id,
ilr_event_result.driver_title,
ilr_events.track_id,
ilr_tracks.turns,
ilr_season.drop_races,
sum(ilr_champ.points - ilr_champ.pen_pts + ilr_champ.bonus_pts) as champ_points,
sum(ilr_champ.laps) as laps_total,
sum(ilr_champ.led) as led_total,
sum(ilr_champ.inc) as inc_total,
sum(ilr_champ.wins) as wins_total,
sum(ilr_champ.poles) as poles_total,
sum(ilr_champ.top5) as top5_total,
sum(ilr_champ.starts) as starts_total,
avg(ilr_champ.avg_s) as avg_s_total,
avg(ilr_champ.avg_f) as avg_f_total,
sum(ilr_champ.km * ilr_champ.laps) as km_total,
sum(ilr_champ.miles * ilr_champ.laps) as miles_total
')
->from('
ilr_event_result,
ilr_champ,
ilr_tracks
')
->join('ilr_season','ilr_season.season_id = ilr_champ.season_id')
->join('ilr_events', 'ilr_events.track_id = ilr_tracks.track_id')
->group_by('ilr_event_result.iracing_cust_id')
->order_by('champ_points desc, driver_title asc')
->get();
return $show_champ->result();sub query problems SOLVED - El Forum - 04-28-2010
[eluser]srpurdy[/eluser]
Just to give you guys an example of what this query is doing
http://ileaguerace.com/champ/show_champ/iRacing-DWC-Road-Course/55
Hopefully that makes it a little easier to understand what I am trying to do.
Which is allow worst total points for example to not count on the points total. So if the championship has 10 races, id like to be able to only count 9 lets say. Dropping the worst race for each driver from the stats. Although how many drop off can be any number. So it can be 5 of 10 that count.
Hope that makes it a bit clearer.
sub query problems SOLVED - El Forum - 04-28-2010
[eluser]Caio Russo[/eluser]
[quote author="srpurdy" date="1272462560"]
Which is allow worst total points for example to not count on the points total. So if the championship has 10 races, id like to be able to only count 9 lets say. Dropping the worst race for each driver from the stats. Although how many drop off can be any number. So it can be 5 of 10 that count.
Hope that makes it a bit clearer.[/quote]
maybe you can make something like this:
Code:
sum(ilr_champ.points - ilr_champ.pen_pts + ilr_champ.bonus_pts) - (select min(ilr_champ.pen_pts) from table where driver_id = a.driver_id) //where a.driver corresponds to the current driver's record that's the driver at the moment.it's something like that?
sub query problems SOLVED - El Forum - 04-28-2010
[eluser]srpurdy[/eluser]
[quote author="Caio Russo" date="1272484084"][quote author="srpurdy" date="1272462560"]
Which is allow worst total points for example to not count on the points total. So if the championship has 10 races, id like to be able to only count 9 lets say. Dropping the worst race for each driver from the stats. Although how many drop off can be any number. So it can be 5 of 10 that count.
Hope that makes it a bit clearer.[/quote]
maybe you can make something like this:
Code:
sum(ilr_champ.points - ilr_champ.pen_pts + ilr_champ.bonus_pts) - (select min(ilr_champ.pen_pts) from table where driver_id = a.driver_id) //where a.driver corresponds to the current driver's record that's the driver at the moment.it's something like that?[/quote]
Thanks for posting
hmm more like
Code:
->select{'champ_points')->limit('3')->orderby('ilr_champ.points', 'desc')but without limiting how many rows are outputed, just the amount of rows that are used for totaling the line below.
Code:
sum(ilr_champ.points - ilr_champ.pen_pts + ilr_champ.bonus_pts) as champ_points,Hope that makes it clearer.
sub query problems SOLVED - El Forum - 05-01-2010
[eluser]Caio Russo[/eluser]
Sorry for the delay. ehehe. I gonna think about. can you send the table script and some data for tests?
sub query problems SOLVED - El Forum - 05-02-2010
[eluser]srpurdy[/eluser]
[quote author="Caio Russo" date="1272776416"]Sorry for the delay. ehehe. I gonna think about. can you send the table script and some data for tests?[/quote]
sent you a pm, thanks for helping, appreciate it greatly! I been messing with this on and off, and not getting any closer, a fresh set of eyes might be just what is needed hehe.
Shawn
sub query problems SOLVED - El Forum - 05-04-2010
[eluser]Caio Russo[/eluser]
Hi Shaw,
I just saw the message now. Soon I work on then I let you know.
Caio
sub query problems SOLVED - El Forum - 05-08-2010
[eluser]srpurdy[/eluser]
[quote author="Caio Russo" date="1273004891"]Hi Shaw,
I just saw the message now. Soon I work on then I let you know.
Caio[/quote]
Hi Caio,
Cool, Appreciate it. I'll likely be working on this a bit soon too see if I can figure it out. I know it'll be harder for you figuring out how I have the thing structured lol.
I have an idea, but I'm not sure yet if it will work, which is create a second function that only grabs all the points rows, with a where variable to get them by a specific field like Driver Name. Than prevent the output of rows with limit($num) which than I can for each loop that result into my view file. So I would hopefully end up with two for each loops. One doing what it's doing now minus the points field, and one doing the points field. With the right variables I don't see why it wouldn't work. Than again it might not be as simple as that! I'm hoping it is though.
Shawn
sub query problems SOLVED - El Forum - 05-10-2010
[eluser]Caio Russo[/eluser]
Hi Shawn ,
I made some tests here with sql, but I can't get what you need directly. What u talk in your last message could be better: make a second function to make the other select seems to be the best way.
I will make some more tests, but work in the "plan B"
cheers,
Caio
sub query problems SOLVED - El Forum - 05-10-2010
[eluser]srpurdy[/eluser]
[quote author="Caio Russo" date="1273530484"]Hi Shawn ,
I made some tests here with sql, but I can't get what you need directly. What u talk in your last message could be better: make a second function to make the other select seems to be the best way.
I will make some more tests, but work in the "plan B"
cheers,
Caio[/quote]
Cool yeah, might be how to do it.
The main problem I noticed I was having was trying to add whatever results I get for each driver together via php. I don't think I can use mysql sum() as than I can't limit how many rows it returns. With the variables in question I'll always get whatever results that exist for that championship.
My only other idea is maybe create another field that does a check through each result when a new one is uploaded (Csv import) this is another function al together which checks which races should not count. using a flag like enum 'Y','N' do a count loop on that so it will add that flag to 'Y' for the results that shouldn't count, based on a database value. That way I can easly add to my query to not bother including those rows that have that flag on. Seems like the easiest way?
So basically id have a new sql field drop(enum 'y','n')
a new where clause where('ilr_champ.drop', 'N')
So it skips over results that have Y
Shawn