foxpro - SQL query to select a column with expression of non-aggregate value and aggregate function -


tables used:

1) v(date d, name c(25), desc c(50), debit n(7), credit n(7))

name in 'v' refers name in vn table

2) vn(date d, name c(25), type c(25), obal n(7))

name in 'vn' primary key , different names grouped type

ex: names abc, def, ghi belongs type 'bank', names xyz, pqr belongs type 'ledger', ...

i've query this: 

select vn.type, sum(vn.obal + iif(v.date < sd, v.credit-v.debit, 0)) opbal, ; sum(iif(between(v.date, sd, ed), v.credit-v.debit, 0)) curbal ; v, vn v.name = vn.name group vn.type ; order vn.type having opbal + curbal != 0

it works fine problem is, obal value entered once per name in table 'vn' query every calculation of credit-debit in table 'v', obal added multiple times , displayed under opbal. when query modified below:

select vn.type, vn.obal + sum(iif(v.date < sd, v.credit-v.debit, 0)) opbal, ; sum(iif(between(v.date, sd, ed), v.credit-v.debit, 0)) curbal ; v, vn v.name = vn.name group vn.type ; order vn.type having opbal + curbal != 0

it shows error message 'group clause missing or invalid'!

rdbms used ms visual foxpro 9. sd , ed date type variables used purpose of query sd < ed.

please me out getting expected result. lot.

i saw sql syntax sql vfp first time few minutes ago, full of errors, 'guessful hunch':

select vn.type, sum(vn.obal + (select sum(iif(v.date < sd, v.credit-v.debit, 0)) v v.name = vn.name)) opbal, sum(select sum(iif(between(v.date, sd, ed), v.credit-v.debit, 0)) v v.name = vn.name) curbal vn group vn.type order vn.type having opbal + curbal != 0

basically, i've turned selection v subselects avoid vn.obal repeated. shouldn't matter v first gets sum individual person before summing them together.


Comments

Post a Comment

Popular posts from this blog

javascript - backbone.js Collection.add() doesn't `construct` (`initialize`) an object -

<!doctype html public "-//w3c//dtd xhtml 1.0 strict//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-strict.dtd"> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script> <script src="http://documentcloud.github.com/underscore/underscore-min.js"></script> <script src="http://documentcloud.github.com/backbone/backbone-min.js"></script> </head> <body> <button id="cmd_create_event" name="cmd_create_event" type="button">create new `event`</button> <script type="text/javascript"> var eventmodel = backbone.model.extend({ initialize : function() { console.log("`event` initialized. id: " + this.cid); this.bind("change:status", function() { console.log(this.get("status") + " value `status`"); }); this.bind("error", function(model, e...
Read more

php - Get uncommon values from two or more arrays -

is there function in php give array of uncommon values 2 or more arrays? for example: $array1 = array( "green", "red", "blue"); $array2 = array( "green", "yellow", "red"); .... $result = function_needed($array1, $array2,...); print_r($result); should give output: array("blue", "yellow", ...); use array_diff , array_merge : $result = array_merge(array_diff($array1, $array2), array_diff($array2, $array1)); here's demo. for multiple arrays, combine callback , array_reduce : function unique(&$a, $b) { return $a ? array_merge(array_diff($a, $b), array_diff($b, $a)) : $b; } $arrays = array( array('green', 'red', 'blue'), array('green', 'yellow', 'red') ); $result = array_reduce($arrays, 'unique'); and here's demo of that.
Read more

Adding duplicate array rows in Php -

i create function in php adds rows of array shared column value. so input. $test = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); and output. $testduplicatefree = array( array("c", 7, 9), array("test", 5, 6) ) ); i'm thinking function combine_duplicates($array,$col){ ... ... return $duplicatefreearray; } where $col duplicate free array. so, in case, combine_duplicates($test,0); would me desired output. this. <?php function combine_duplicates($array,$col) { $index = array(); foreach ($array $row) { $key = $row[$col]; if (!isset($index[$key])) { $index[$key] = $row; } else { ($i = 0; $i < count($row); ++$i) { if ($i != $col) { $index[$key][$i] += $row[$i]; } } } } return array_values($index); } $array = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); print_r(combine_duplicates($array, 0)); try here: http://codepad.org/mdhesqhi
Read more