sql - row counter with condition in two different columns -


i have following tables sport results (e.g. football):

tblgoals (rowid, gamerowidm playerrowid, teamrowid, goalminute)

rowid | gamerowid | playerrowid | teamrowid | goalminute -------------------------------------------------------- 1     | 1         | 1           | 1         | 25 2     | 1         | 2           | 2         | 45 3     | 1         | 3           | 1         | 66

tblplayers (rowid, playername)

rowid | playername ------------------ 1     | john snow 2     | frank underwood 3     | jack bauer

tblgames (rowid, teamhomerowid, teamguestrowid)

rowid | teamhomerowid | teamguestrowid | gamedate --------------------------------------------------- 1     | 1             | 2              | 2015-01-01

now want list of goals. list should this:

goalminute | playername      | goalshome | goalsguest ----------------------------------------------------- 25         | john snow       | 1         | 0 45         | frank underwood | 1         | 1 66         | jack bauer      | 2         | 1

goalshome , goalsguest should counter of shot goals team. e.g. if check last row, result 2:1 home team. list of goals, used statement:

select  t_gol.goalminute,         t_ply.playername,         case when             t_gol.teamrowid = t_gam.teamhomerowid              row_number()  on (partition t_gam.teamhomerowid order t_gam.teamhomerowid)         end goalshome,         case when             t_gol.teamrowid = t_gam.teamguestrowid              row_number()  on (partition t_gam.teamguestrowid order t_gam.teamguestrowid)         end goalsguest    dbo.tblgoalsfussball t_gol left join dbo.tblplayersfussball t_ply on (t_ply.rowid = t_gol.playerrowid) left join dbo.tblgames t_gam on (t_gam.rowid = t_gol.gamerowid)   t_gol.gamerowid = @match_row

but here:

goalminute | playername      | goalshome | goalsguest ----------------------------------------------------- 25         | john snow       | 1         | null 45         | frank underwood | null      | 2 66         | jack bauer      | 3         | null

maybe row_number() wrong approach?

i running total using sum() windowed aggregate function over ... clause, works in sql server 2012+. 

select      g.rowid, g.gamedate, t.goalminute, p.playername,      goalshome = coalesce(sum(case when teamrowid = g.teamhomerowid 1 end) on (partition gamerowid order goalminute),0),     goalsguest = coalesce(sum(case when teamrowid = g.teamguestrowid 1 end) on (partition gamerowid order goalminute),0)  tblgoals t join tblplayers p on t.playerrowid = p.rowid join tblgames g on t.gamerowid = g.rowid order t.gamerowid, t.goalminute

another approach (that works in older versions) use self-join , sum rows lower goalminutes. ease of reading i've used common table expression split goals 2 columns home , guest team:

;with t (     select         g.goalminute, g.playerrowid, g.gamerowid,         case when teamrowid = ga.teamhomerowid 1 end homegoals,        case when teamrowid = ga.teamguestrowid 1 end guestgoals     tblgoals g     join tblgames ga on g.gamerowid = ga.rowid )  select      g.rowid, g.gamedate, t.goalminute, p.playername,      goalshome  = (select sum(coalesce(homegoals,0)) t t2 t2.goalminute <= t.goalminute , t2.gamerowid = t.gamerowid),     goalsguest = (select sum(coalesce(guestgoals,0)) t t2 t2.goalminute <= t.goalminute , t2.gamerowid = t.gamerowid) t join tblplayers p on t.playerrowid = p.rowid join tblgames g on t.gamerowid = g.rowid order t.gamerowid, t.goalminute

the cte isn't necessary though, use derived table

sample sql fiddle


Comments

Post a Comment

Popular posts from this blog

How to connect android app to App engine -

i'm trying connect app in android server i've opened on appengine. my server's name : dddd-daniel-2345 , website server : http://dddd-daniel-2345.appspot.com/ this how connect android app server in app engine: app.yaml: application: dddd-daniel-2345 version: 1 runtime: python27 api_version: 1 threadsafe: yes handlers: - url: .* script: main.app libraries: - name: webapp2 version: "2.5.2" main.py: #!/usr/bin/env python # # copyright 2007 google inc. google.appengine.api import users import webapp2 class userhandler(webapp2.requesthandler): def get(self): user = users.get_current_user() if user: greeting = ('%s,%s,%s' % (user.email(),user.user_id(),user.nickname())) else: greeting = ('not logged in') self.response.out.write(greeting) app = webapp2.wsgiapplication([('/', userhandler),], debug=true) my applications code : add manifast main....
Read more

gcc - MinGW's ld cannot perform PE operations on non PE output file -

i know there other similar questions out there, stackoverflow or not. i've researched lot this, , still didn't find single solution. i'm doing operative system side project. i've been doing in assembly, wanna join c code. test, made assembly code file (called test.asm): [bits 32] global _a section .text _a: jmp $ then made c file (called main.c): extern void a(void); int main(void) { a(); } to link, used file (called make.bat): "c:\mingw\bin\gcc.exe" -ffreestanding -c -o c.o main.c nasm -f coff -o asm.o test.asm "c:\mingw\bin\ld.exe" -ttext 0x100000 --oformat binary -o out.bin c.o asm.o pause i've been researching ages, , i'm still struggling find answer. hope won't flagged duplicate. acknowledge existence of similar questions, have different answers, , none work me. question: what doing wrong? old mingw versions had problem "ld" not able create non-pe files @ all. maybe current versions...
Read more

php - display validation error message next to the textbox in codeigniter -

Image
i'm newbie codeigniter.i want display error message next text box.my view file create.php contains following code. <?php echo validation_errors(); //validation echo form_open('news/create'); //create form ?> <div class="boxes"> <div class="common"> <div class="lables"> <label for="name">name</label> </div> <div class="text_boxes"> <input type="text" name="uname" /><div id="infomessage"><?php echo $message;?></br> </div> </div>` and below controller model: public function create() { $this->load->helper('form'); $this->load->library('form_validation'); $data['title'] = 'insert items'; $this->form_validation->set_rules('uname','required|min_length[6]|m...
Read more