mysql - sum of value of each month of year -

-

i want query in mysql sum of value every month on year.

currently have this:

select     e.rfc rfc,     sum(f.total) total,     month(f.fecha) mes      foo f  inner join     bar e          on f.bar_id = e.id  inner join     baz u          on e.baz_id = u.id      u.id = 3      , date(f.fecha) between '2014-01-01' , '2014-12-31'  group     month(f.fecha)

but in months doesn't exist foo values not showing.

my result atm it's like:

rfc             total           mes aaa010101aaa    10556.000000    12 aaa010101bbb    1856.000000     11 aaa010101bbb    66262.896800    10 aaa010101bbb    990.090000      9 aaa010101bbb    73.000000       8 aaa010101bbb    1304761.620000  7

my desired result are:

rfc             total           mes aaa010101aaa    10556.000000    12 aaa010101aaa    0.0             11 ... (when no data it's available return 0.0 month) aaa010101aaa    0.0             1 aaa010101bbb    0.0             12 aaa010101bbb    1856.000000     11 aaa010101bbb    66262.896800    10 aaa010101bbb    990.090000      9 aaa010101bbb    73.000000       8 aaa010101bbb    1304761.620000  7 aaa010101bbb    0.0             6 ... aaa010101bbb    0.0             1

i want fill chart , need 0 when no foo values available sum.

thank you.

assuming have data user in each month each rfc, easy way fix using conditional aggregation:

select e.rfc rfc,        sum(case when u.id = 3 f.total else 0 end) total,        month(f.fecha) mes  foo f inner join      bar e       on f.bar_id = e.id inner join      baz u       on e.baz_id = u.id  date(f.fecha) between '2014-01-01' , '2014-12-31'  group month(f.fecha) ;

if doesn't work, have start mucking around left join , generating rows 12 months of each each rfc.

edit:

here more painful version:

select sum(total), m.m (select distinct month(f.fecha) m foo date(f.fecha) between '2014-01-01' , '2014-12-31' ) m left join      foo f      on month(f.fecha) = m.m left join      bar e       on f.bar_id = e.id left join      baz u       on e.baz_id = u.id , u.id = 3 group m.m order m.m;

i don't know rfc value doing. coming arbitrary row , doesn't belong in query.


Comments

Post a Comment

Popular posts from this blog

python - mat is not a numerical tuple : openCV error -

-
i have write down code showing error ma not getting it: please help: showing mat not numerical tuple: import cv import cv2 capture = cv2.videocapture("j.3gp") while(1): _, frame1 = capture.read() grayimage1 = cv2.cvtcolor(frame1, cv2.color_bgr2gray) _, frame2 = capture.read() grayimage2 = cv2.cvtcolor(frame2, cv2.color_bgr2gray) differenceimage = cv2.absdiff(grayimage1,grayimage2) thresholdimage = cv2.threshold(differenceimage,25,255,cv2.thresh_binary) cv2.imshow("difference image", differenceimage) cv2.imshow("threshold image", thresholdimage) cv2.imshow("image", frame1) k = cv2.waitkey(30) & 0xff error arising : ----------------------------------------------------------------------------------------- traceback (most recent call last): file "desk.py", line 15, in <module> cv2.imshow("threshold image", thresholdimage) typeerror: mat not numerical tuple ...
Read more

c# - MSAA finds controls UI Automation doesn't -

-
i'm working on automating windows application. i'm using teststack white framework. i've hit problem. program has 'window' object cannot see inside of. white shows no controls inside of it. inspect.exe shows no controls inside of either when running in ui automation mode. if switch inspect msaa see controls inside fine. there anyway me use msaa c# handle on these controls? if can identify msaa functions need, can use p/invoke call them c#. here's example article doing msaa: http://www.codeproject.com/articles/38906/ui-automation-using-microsoft-active-accessibility also, pinvoke.net can used identify iaccessible (msaa) functions: http://www.pinvoke.net/search.aspx?search=iaccessible&namespace=[all] here's answer along lines: msaa com-based? finally, alternative p/invoke, might able use tlbimp.exe create wrapper assembly oleacc.dll, , access msaa functions through it. i'm not sure if works msaa, it's worth try. as e...
Read more

wordpress - .htaccess: RewriteRule: bad flag delimiters -

-
pulling hair out on one. have following .htaccess file custom redirects in addition wordpress's default settings. when tested on local wamp server worked fine after moving production i'm not getting rewriterule: bad flag delimiters error in server log , after while site goes down internal server error any appreciated. in advance! <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^rockford_weddings___welcome\.html$ /wedding/ [r=301,l] rewriterule ^blog/?$ /journal/ [r=301,l] rewriterule ^blog/(.*)$ /$1 [r=301,l] rewriterule ^portraitinvestment/?$ /portraitinvestment\.pdf [r=301,l] rewriterule ^weddinginvestment/?$ /weddinginvestment\.pdf [r=301,l] rewriterule ^holidaycards2014/?$ /holidaycards2014\.pdf [r=301,l] </ifmodule> # begin wordpress <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^index\.php$ - [l] rewritecond %{request_filename} !-f rewritecond %{request_filename} !-d rewriterule . /index.php [l] </...
Read more