date - MYSQL - SUM values of last 10 days -


i have table in mysql:

|   player1   |  player2 |    date    |   fs_1   |   fs_2   |     jack         tom       2015-03-02       10         2     mark        riddley    2015-05-02       3          1       ...

i need know how many aces (fs_1) player 1 have done before match reported in date_g (10 days before example).

this tried without success:

option 1

    select      players_atp.name_p 'player 1',     p.name_p 'player 2',     date(date_g) 'date',     result_g 'result',     fs_1,     fs_2,     sum(if(date_sub(date_g, interval 10 day)< date_g, fs_1, 0)) 'last 10 days'     stat_atp stat_atp         join     backup3.players_atp on id1 = id_p         join     backup3.players_atp p on p.id_p = id2         join     backup3.games_atp on id1_g = id1 , id2_g = id2         , id_t_g = id_t         , id_r_g = id_r     date_g > '2015-01-01' group id1;

option 2

select      players_atp.name_p 'player 1',     p.name_p 'player 2',     date(date_g) 'date',     result_g 'result',     fs_1,     fs_2,     sum(case when date_g between date_g , date_sub(date_g, interval 10 day) fs_1 else 0 end) 'last 10 days'     stat_atp stat_atp         join     backup3.players_atp on id1 = id_p         join     backup3.players_atp p on p.id_p = id2         join     backup3.games_atp on id1_g = id1 , id2_g = id2         , id_t_g = id_t         , id_r_g = id_r     date_g > '2015-01-01' group id1;

i have edited code, more easy read , understand.

 select       id1 'player 1',      id2 'player 2',      date(date_g) 'date',      result_g 'result',      fs_1,      fs_2,      sum(case          when date_g between date_g , date_sub(date_g, interval 10 day) fs_1      end) 'last 20 days'      stat_atp stat_atp          join      backup3.games_atp on id1_g = id1 , id2_g = id2          , id_t_g = id_t          , id_r_g = id_r group id1;

thanx in advance.

maybe you:

select      id1,     sum(fs_1)       stat_atp      date_g <= date_sub('2015-03-02', interval 1 day) , date_g >= date_sub('2015-03-02', interval 10 day) ,     id1='jack' group id1;

remember rdbms used construct rigorous data sets linked between each others clear ids (keys when talking sql). it's easier respect 3 first normal forms. that's why should use keys identify match itself. way use subqueries (subsets) achieve goal.

then, keep in mind sql structured. it's force , weakness cause won't able use turing complete programming langage loops , conditions. in situation able find same structure query. can interact sql query result langage , use loops , condition on result set itself. that's you.

anyway, may want read mysql group clause different iso sql form : https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html


-

Comments

Post a Comment

Popular posts from this blog

python - How to insert QWidgets in the middle of a Layout? -

Image
i'm using qt framework build graphical user interface. use qgridlayout position qwidgets neatly. gui looks this: my application regularly adds new widgets gui @ runtime. these new widgets not added @ end of qlayout, somewhere in middle. the procedure bit cumbersome. applied on figure above, need take out widg_c , widg_d , ... qgridlayout. next, add widg_x and widg_y , , put other widgets again. how remove widgets qgridlayout: for in reversed(range(mygridlayout.count())): self.itemat(i).widget().setparent(none) ### as long you're dealing small amount of widgets, procedure not disaster. in application display lot of small widgets - perhaps 50 or more! application freezes second while procedure ongoing, annoying user. there way insert widgets somewhere in qlayout, without need take out other widgets? edit: apparently solution qvboxlayout simple. use function insertwidget(..) instead of addwidget(..) . docs can found link: http://doc.qt.io/qt-5/qboxlayou
Read more

python - serve multiple gunicorn django instances under nginx ubuntu -

i need serve 2 webs apps (django) on gunicorn under different domain names using nginx on ubuntu. started using tutorial: https://www.digitalocean.com/community/tutorials/how-to-set-up-django-with-postgres-nginx-and-gunicorn-on-ubuntu-16-04 it worked fine 1 using that. tried same thing second gives me 502 gateway second domain. tried following tutorial: http://michal.karzynski.pl/blog/2013/10/29/serving-multiple-django-applications-with-nginx-gunicorn-supervisor/ i'm @ part run gunicorn start script: sudo bin/gunicorn_start i response: starting webuildblack root traceback (most recent call last): file "/home/devin/webuildblack/bin/gunicorn", line 7, in <module> gunicorn.app.wsgiapp import run importerror: no module named 'gunicorn.app'; 'gunicorn' not package you have named program gunicorn.py gunicorn -package. rename file , remove gunicorn.pyc file.
Read more

module - Prestashop displayPaymentReturn hook url -

i new prestashop developer , trying create paymentmodule. have got show payment method can not proceed purchase because not know hot works. does know should redirect run hooddisplaypaymentreturn method? i happy if explain me complete navigation map make purchase. anyway, can find relation between hooks , pages? when have new module payment rely on simplest provided prestashop: bankwire. inside can find 3 hooks. hookpayment: public function hookpayment($params) { if (!$this->active) return; if (!$this->checkcurrency($params['cart'])) return; $this->smarty->assign(array( 'this_path' => $this->_path, 'this_path_bw' => $this->_path, 'this_path_ssl' => tools::getshopdomainssl(true, true).__ps_base_uri__.'modules/'.$this->name.'/' )); return $this->display(__file__, 'payment.tpl'); } hookdisplaypaymenteu: public fun
Read more