Geek asks and answers

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

Used tables:

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

name in 'v' stands for name in table vn

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

name in 'vn' is the primary key, and different names are grouped by type

ex: the names abc, def, ghi are of the type "bank", the names xyz, pqr are of the type "register", ...

I have a query like 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 ;
    FROM v, vn WHERE v.name = vn.name GROUP BY vn.type ;
    ORDER BY vn.type HAVING OpBal + CurBal != 0

, , obal - , 'vn', 'v', obal OpBal. :

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 ;
    FROM v, vn WHERE v.name = vn.name GROUP BY vn.type ;
    ORDER BY vn.type HAVING OpBal + CurBal != 0

, " Group by "!

RDBMS MS Visual Foxpro 9. sd ed - , , sd <

, . .

+6
3

SQL Syntax SQL VFP , , " ":

SELECT vn.type, 
       SUM(vn.obal + (SELECT SUM(IIF(v.date < sd, v.credit-v.debit, 0)) 
                      FROM v 
                      WHERE v.name = vn.name)) OpBal,
       SUM(SELECT SUM(IIF(BETWEEN(v.date, sd, ed), v.credit-v.debit, 0))
           FROM v 
           WHERE v.name = vn.name) CurBal
FROM vn
GROUP BY vn.type
ORDER BY vn.type 
HAVING OpBal + CurBal != 0

, v , vn.obal. v , , .

0

. VFP 9 , , MySQL, . , , , PK (, , ..).

SET ENGINEBEHAVIOR 80

VFP 9

SET ENGINEBEHAVIOR 90

, , , .

... , , ... 3 VFP... "", "" "", alias.column.

() , . "sd" ( ) "ed" ( )

CREATE CURSOR vn;
   ( date d, ;
     name c(25), ;
     type c(25), ;
     obal n(7) )

INSERT INTO vn VALUES ( CTOD( "5/20/2012" ), "person 1", "person type 1", 125 )
INSERT INTO vn VALUES ( CTOD( "5/20/2012" ), "person 2", "another type ", 2155 )

CREATE CURSOR v;
   ( date d, ;
     name c(25), ;
     desc c(50), ;
     debit n(7), ;
     credit n(7))

INSERT INTO V VALUES ( CTOD( "6/1/2012" ), "person 1", "description 1", 10, 32 )
INSERT INTO V VALUES ( CTOD( "6/2/2012" ), "person 1", "desc 2", 235, 123 )
INSERT INTO V VALUES ( CTOD( "6/3/2012" ), "person 1", "desc 3", 22, 4 )
INSERT INTO V VALUES ( CTOD( "6/4/2012" ), "person 1", "desc 4", 53, 36 )
INSERT INTO V VALUES ( CTOD( "6/5/2012" ), "person 1", "desc 5", 31, 3 )
INSERT INTO V VALUES ( CTOD( "6/1/2012" ), "person 2", "another 1", 43, 664 )
INSERT INTO V VALUES ( CTOD( "6/4/2012" ), "person 2", "more desc", 78, 332 )
INSERT INTO V VALUES ( CTOD( "6/6/2012" ), "person 2", "anything", 366, 854 )

sd = CTOD( "6/3/2012" )      && start date of transactions
ed = DATE()  && current date as the end date...

... , () FIRST. , , (sd) , /, . , "obal" "vn". , "MAX()" . PK (), , , , ...

select;
      vn.name,;
      vn.type,;
      MAX( vn.obal ) as BalByNameOnly,;
      SUM( IIF( v.date < sd, v.credit-v.debit, 000000.00 )) OpBal, ;
      SUM( IIF( BETWEEN(v.date, sd, ed), v.credit - v.debit, 000000.00 )) CurBal ;
   FROM ;
      v,;
      vn ;
   WHERE ;
      v.name = vn.name;
   GROUP BY ;
      vn.Name,;
      vn.Type;
   INTO ;
      CURSOR C_JustByName READWRITE

( ) ...

Name      Type            BalByNameOnly   OpBal   CurBal
person 1  person type 1       125         -90     -63 
person 2  another type       2155         621     742

, "" (C_JustByName) , ..... -

SELECT ;
      JBN.type, ;
      JBN.BalByNameOnly - JBN.OpBal as OpBal,;
      JBN.CurBal ;
    FROM ;
       C_JustByName JBN ;
    GROUP BY ;
       vn.type ;
    ORDER BY ;
       vn.type ;
    HAVING ;
       OpBal + CurBal != 0;
    INTO ;
       CURSOR C_Final

, , "VN" (, ) , oBal .

VFP , , , ... , . , , EXPECTING, ...

, , .

0

, :( , :

, 6 :( , . . 1) , . . - . 2) , , . . date(), datetime(), date\datetime.

, .

, , .

OP : "

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 ;
    FROM v, vn WHERE v.name = vn.name GROUP BY vn.type ;
    ORDER BY vn.type HAVING OpBal + CurBal != 0

. "

, , , SQL, , , . , . , OP:

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

INSERT INTO vn (date, name, type, obal) VALUES ( DATE(2012,5,20), "abc", "bank", 100 )
INSERT INTO vn (date, name, type, obal) VALUES ( DATE(2012,5,20), "def", "bank", 200 )
INSERT INTO vn (date, name, type, obal) VALUES ( DATE(2012,5,20), "ghi", "bank", 300 )
INSERT INTO vn (date, name, type, obal) VALUES ( DATE(2012,5,20), "xyz", "ledger", 400 )
INSERT INTO vn (date, name, type, obal) VALUES ( DATE(2012,5,20), "pqr", "ledger", 500 )

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

INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,1), "abc", "description 1", 50, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,2), "abc", "description 1", 60, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,3), "abc", "description 1", 70, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,1), "def", "description 1", 50, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,2), "def", "description 1", 60, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,3), "def", "description 1", 70, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,1), "ghi", "description 1", 50, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,2), "ghi", "description 1", 60, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,4), "xyz", "description 1", 50, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,5), "xyz", "description 1", 60, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,6), "pqr", "description 1", 50, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,7), "pqr", "description 1", 60, 0 )
INSERT INTO V (date,name,desc,debit,credit) VALUES ( DATE(2012,6,8), "pqr", "description 1", 70, 0 )

OP- :

LOCAL SD, sd = (2012,6,1) & ed = DATE() & ...

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 ;
    FROM v, vn WHERE v.name = vn.name GROUP BY vn.type ;
    ORDER BY vn.type HAVING OpBal + CurBal != 0

, :

TYPE    OPBAL   CURBAL
------- -----   ------
bank     1500     -470
ledger   2300     -290

. , , . , , , , :

SELECT vn.type, vn.name, v.date, vn.obal, v.credit, v.debit ;
    FROM v, vn ;
    WHERE v.name = vn.name

:

  TYPE     NAME DATE         OBAL CREDIT DEBIT
  bank     abc  06/01/2012    100      0    50
  bank     abc  06/02/2012    100      0    60
  bank     abc  06/03/2012    100      0    70
  bank     def  06/01/2012    200      0    50
  bank     def  06/02/2012    200      0    60
  bank     def  06/03/2012    200      0    70
  bank     ghi  06/01/2012    300      0    50
  bank     ghi  06/02/2012    300      0    60
  ledger   xyz  06/04/2012    400      0    50
  ledger   xyz  06/05/2012    400      0    60
  ledger   pqr  06/06/2012    500      0    50
  ledger   pqr  06/07/2012    500      0    60
  ledger   pqr  06/08/2012    500      0    70

, , , 'abc' OBal 100 3 , 3 v. Summing 300, 100.

When you use an aggregate, such as SUM () or AVG (), you must first perform aggregation without combining, and then perform aggregation. You can still perform aggregation, provided the PROVIDED join provides a 1-to-many relationship. If the above results were:

  TYPE   NAME OBAL CREDIT DEBIT
  bank    abc  100      0   180
  bank    def  200      0   180
  bank    ghi  300      0   110
  ledger  xyz  400      0   110
  ledger  pqr  500      0   180

it would be normal to use the SUM () BY type (1 side from 1-to-many).

Having said that and adding that VFP supports subqueries, let's write a solution:

Local sd,ed
sd = Date(2012,6,1)  && start date of transactions
ed = Date()          && current date as the end date...

Select vn.Type, Sum(vn.OBal - tmp.TotCd) As Opbal, Sum(tmp.Curbal) As Curbal ;
    FROM vn ;
    LEFT Join ;
       (Select v.Name, Sum(Iif(v.Date < sd, v.credit-v.debit, 0)) TotCd, ;
               SUM(Iif(Between(v.Date, sd, ed), v.credit-v.debit, 0)) Curbal ;
        FROM v ;
        GROUP By v.Name ) tmp On tmp.Name = vn.Name ;
    GROUP By vn.Type ;
    ORDER By vn.Type ;
    HAVING Sum(vn.OBal - tmp.TotCd + tmp.Curbal) != 0

we get what we want:

TYPE    OPBAL   CURBAL
------- -----   ------
bank      600     -470
ledger    900     -290
0
source

More articles:


All Articles