sql - applying DISTINCT SUM -


i'm working on code me sum amount of gl sub heads (that's accounting term) in table. things is, there multiple rows same gl sub head different amounts. need sum amounts of table per gl sub head. of course, involves use of sum , distinct clauses. here's have far:

select      gstt.bal_date,     sum(gstt.tot_dr_bal) totaldr,     sum(gstt.tot_cr_bal) totalcr  from(      select distinct          gam.gl_sub_head_code,          gstt.tot_dr_bal,         gstt.tot_cr_bal      tbaadm.gam      left outer join tbaadm.eab          on gam.acid = eab.acid          , gam.bank_id = eab.bank_id      left outer join tbaadm.gstt         on gam.sol_id = gstt.sol_id          , gam.bank_id = gstt.bank_id         , gam.gl_sub_head_code = gstt.gl_sub_head_code         , gam.acct_crncy_code = gstt.crncy_code               gam.acct_ownership = 'o'          --and eab.eod_date = to_date('3/24/2014', 'mm/dd/yyyy')         , gstt.bal_date = to_date('3/24/2014', 'mm/dd/yyyy')     ) group gstt.bal_date, totaldr, totalcr

can't make bloody thing work, though. know i'm missing something, can't put finger on is, , should put in code. appreciated. if need further clarifications, ask.

edit

i've got code work now, somewhat. because there's problem.

select      bal_date,     gl_sub_head_code,     sum(tot_dr_bal),     sum(tot_cr_bal)  from(      select distinct          gam.gl_sub_head_code,          gstt.tot_dr_bal,         gstt.tot_cr_bal,         gstt.bal_date      tbaadm.gam      left outer join tbaadm.eab          on gam.acid = eab.acid          , gam.bank_id = eab.bank_id      left outer join tbaadm.gstt         on gam.sol_id = gstt.sol_id          , gam.bank_id = gstt.bank_id         , gam.gl_sub_head_code = gstt.gl_sub_head_code         , gam.acct_crncy_code = gstt.crncy_code               gam.acct_ownership = 'o'          --and eab.eod_date = to_date('3/24/2014', 'mm/dd/yyyy')         , gstt.bal_date = to_date('3/24/2014', 'mm/dd/yyyy')     ) group bal_date, gl_sub_head_code

as can see, there distinct clause @ beginning of subquery. whenever remove that, toad outputs same set of results. same results regardless if have distinct clause or not. have feeling there's wrong code, i'm expecting distinct should make difference.

"i same results regardless if have distinct clause or not."

this means query returns unique set of rows.

i have feeling there's wrong code, i'm expecting distinct should make difference.

why think that?

distinct made difference in first query because hadn't included bal_date in sub-query's projection. have gl_sub_head instances balances same multiple days.

except filtering on bal_date should 1 value anyway. hmmmm....

clearly awry in query. there 2 obvious puzzlers.

  1. why have left outer join gstt? hard filter on gstt.bal_date means return records gstt.bal_date not null, result set still same if inner join.

  2. why bother querying eab @ all? don't use values , left outer join means doesn't restrict values retrieved gam in anyway. plus fact have commented out filter on eab.eod_date suggests produces cross join might generate multiple duplicate values think need distinct eradicate.

so, don't have actual answer you, think need re-visit business logic , figure out query needs do.


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

inno setup - TLabel or TNewStaticText - change .Font.Style on Focus like Cursor changes with .Cursor -

is possible change .font.style on focus tlabel or tnewstatictext happens cursor when use .cursor ? there no built-in support track mouse hovering in inno setup @ time. however, intercepting window procedure of controls can track yourself. following example need innocallback library: [setup] appname=my program appversion=1.5 defaultdirname={pf}\my program outputdir=userdocs:inno setup examples output [files] source: "innocallback.dll"; destdir: "{tmp}"; flags: dontcopy [code] #ifdef unicode #define aw "w" #else #define aw "a" #endif const gwl_wndproc = -4; wm_mousemove = $0200; type wparam = uint_ptr; lparam = longint; lresult = longint; twindowproc = function(hwnd: hwnd; umsg: uint; wparam: wparam; lparam: lparam): lresult; function setcapture(hwnd: hwnd): hwnd; external 'setcapture@user32.dll stdcall'; function releasecapture: bool; external 'releasecapture@user32.dll stdcall'; func...
Read more