Thread: Group BY

Group BY

From

WaGathoni

Date:

29 October 2008, 20:59:50

Justin was recommending a solution to the Chart of Accounts Problem
posted by jamhitz:

MQUOTE>
One has you chart of Accounts
   Create table coa (
      coa_id serial not null,
      parent_id int not null default 0,
      doIhaveChildren boolean default false
       account_name text null )
primary key(coa_id)

Create Table general_ledger_transactions(
      transaction_id serial not null
      coad_id integer,
      accounting_period integer,
      debit numeric(20,10) ,
      credit numeric(20,10),
      transaction_date datestamp)
primary key (transaction_id)

...

Create table  accounting_periods (
   accounting_period serial not null,
   start_date date,
   end_date date,
   accounting_period_Open boolean)

</QUOTE>

Would someone please assist me.  Why is the following function:...


CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
integer, PassedPeriodID integer) RETURNS NUMERIC AS
$FunctionCode$
DECLARE retval NUMERIC :=0.0;
begin
    SELECT Sum(gl_transactions.debit) +
        CASE WHEN coa.doIhaveChildren THEN
            GetChildAccountDebits(coa.coa_id, PassedPeriodID )
        ELSE
                   0.0
        END
    INTO retval
    FROM gl_transactions, coa
    WHERE gl_transactions.coa_id= coa.coa_id
            AND coa.parent_id = PassedAccountID
        AND gl_transactions.period_id = PassedPeriodID;

    RETURN retval;
end;
$FunctionCode$
 LANGUAGE 'plpgsql' VOLATILE ;

....failing with an error to the effect that that that
coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY
clause.... and what is is the recommended course of action.

I have limited Internet access, so forgive me when I raise the same
question 8 days later.

Thanks

Re: Group BY

From

"Scott Marlowe"

Date:

29 October 2008, 22:01:45

On Wed, Oct 29, 2008 at 2:59 PM, WaGathoni <is.mundu@gmail.com> wrote:
> ....failing with an error to the effect that that that
> coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY
> clause.... and what is is the recommended course of action.

Generally the solution in postgresql is its proprietary extension of
distinct on ()

select distinct on (field1, field2) field1, field2, field3 from ....

Re: Group BY and Chart of Accounts

From

justin

Date:

29 October 2008, 23:49:19

There was a number of code mistakes  in my examples as i was just doing
it off the top of my head,  just went through it and got it all working.

I had to change the function around as it was double dipping accounts
just run this and it does work.

--------------------------------------------------------------

Create table coa (
    coa_id serial not null,
    parent_id int not null default 0,
    doIhaveChildren boolean default false,
    account_name text null );


Create Table general_ledger_transactions(
    transaction_id serial not null,
    coa_id integer,
    accounting_period integer,
    debit numeric(20,10) ,
    credit numeric(20,10),
    transaction_date timestamp);


Create table  accounting_periods (
    accounting_period serial not null,
    start_date date,
    end_date date,
    accounting_period_Open boolean);

Insert into coa values (10, default, True, 'ParentAccount1');
Insert into coa values (11, 10, True, 'ChildAccount1');
Insert into coa values (12, 11, false, 'ChildAccount2');
Insert into coa values (13, default, false, 'ChildAccount3');

Insert into Accounting_Periods values ( 1, '2008-10-01', '2008-10-31',
true );
Insert into Accounting_Periods values ( 2, '2008-11-01', '2008-11-30',
true );

Insert into general_ledger_transactions values(  default, 11,  1, 30.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 11,  1, 20.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 12,  1, 10.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 12,  1, 50.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 11,  1, 1.0,
0.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 13,  1, 0.0,
111.0, current_timestamp);


Insert into general_ledger_transactions values(  default, 11,  2, 0.0,
30.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 11,  2, 0.0,
20.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 12,  2, 0.0,
10.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 12,  2, 0.0,
50.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 11,  2, 0.0,
1.0, current_timestamp);
Insert into general_ledger_transactions values(  default, 13,  2, 111.0,
0.0, current_timestamp);




CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
integer, PassedPeriodID integer) RETURNS NUMERIC AS
$FunctionCode$
DECLARE
    retval NUMERIC = 0.0 ;
begin

    return (SELECT
        coalesce ( (select Sum(general_ledger_transactions.debit ) from
general_ledger_transactions where general_ledger_transactions.coa_id =
coa.coa_id and general_ledger_transactions.accounting_period =
PassedPeriodID), 0 ) +
        (CASE WHEN coa.doIhaveChildren THEN
            GetChildAccountDebits(coa.coa_id, PassedPeriodID )
        ELSE
                   0.0
        END)
    FROM coa
       WHERE  coa.parent_id = PassedAccountID);

end;
$FunctionCode$
 LANGUAGE 'plpgsql' VOLATILE ;

select 10, getchildaccountdebits(10,1)
union
select 11, getchildaccountdebits(11,1)
union
select 12, getchildaccountdebits(12,1);


--------------------------------------------------

WaGathoni wrote:
> Justin was recommending a solution to the Chart of Accounts Problem
> posted by jamhitz:
>
> MQUOTE>
> One has you chart of Accounts
>    Create table coa (
>       coa_id serial not null,
>       parent_id int not null default 0,
>       doIhaveChildren boolean default false
>        account_name text null )
> primary key(coa_id)
>
> Create Table general_ledger_transactions(
>       transaction_id serial not null
>       coad_id integer,
>       accounting_period integer,
>       debit numeric(20,10) ,
>       credit numeric(20,10),
>       transaction_date datestamp)
> primary key (transaction_id)
>
> ...
>
> Create table  accounting_periods (
>    accounting_period serial not null,
>    start_date date,
>    end_date date,
>    accounting_period_Open boolean)
>
> </QUOTE>
>
> Would someone please assist me.  Why is the following function:...
>
>
> CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
> integer, PassedPeriodID integer) RETURNS NUMERIC AS
> $FunctionCode$
> DECLARE retval NUMERIC :=0.0;
> begin
>     SELECT Sum(gl_transactions.debit) +
>         CASE WHEN coa.doIhaveChildren THEN
>             GetChildAccountDebits(coa.coa_id, PassedPeriodID )
>         ELSE
>                    0.0
>         END
>     INTO retval
>     FROM gl_transactions, coa
>     WHERE gl_transactions.coa_id= coa.coa_id
>             AND coa.parent_id = PassedAccountID
>         AND gl_transactions.period_id = PassedPeriodID;
>
>     RETURN retval;
> end;
> $FunctionCode$
>  LANGUAGE 'plpgsql' VOLATILE ;
>
> ....failing with an error to the effect that that that
> coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY
> clause.... and what is is the recommended course of action.
>
> I have limited Internet access, so forgive me when I raise the same
> question 8 days later.
>
> Thanks
>
>

Re: Group BY and Chart of Accounts

From

WaGathoni

Date:

30 October 2008, 17:59:35

Works like a charm.  Thank you very much Justin.

On Thu, Oct 30, 2008 at 3:49 AM, justin <justin@emproshunts.com> wrote:
> There was a number of code mistakes  in my examples as i was just doing it
> off the top of my head,  just went through it and got it all working.
> I had to change the function around as it was double dipping accounts
> just run this and it does work.
> --------------------------------------------------------------
>
> Create table coa (
>   coa_id serial not null,
>   parent_id int not null default 0,
>   doIhaveChildren boolean default false,
>   account_name text null );
>
>
> Create Table general_ledger_transactions(
>   transaction_id serial not null,
>   coa_id integer,
>   accounting_period integer,
>   debit numeric(20,10) ,
>   credit numeric(20,10),
>   transaction_date timestamp);
>
>
> Create table  accounting_periods (
>   accounting_period serial not null,
>   start_date date,
>   end_date date,
>   accounting_period_Open boolean);
>
> Insert into coa values (10, default, True, 'ParentAccount1');
> Insert into coa values (11, 10, True, 'ChildAccount1');
> Insert into coa values (12, 11, false, 'ChildAccount2');
> Insert into coa values (13, default, false, 'ChildAccount3');
>
> Insert into Accounting_Periods values ( 1, '2008-10-01', '2008-10-31', true
> );
> Insert into Accounting_Periods values ( 2, '2008-11-01', '2008-11-30', true
> );
>
> Insert into general_ledger_transactions values(  default, 11,  1, 30.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  1, 20.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  1, 10.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  1, 50.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  1, 1.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 13,  1, 0.0,
> 111.0, current_timestamp);
>
>
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 30.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 20.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  2, 0.0, 10.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  2, 0.0, 50.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 1.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 13,  2, 111.0,
> 0.0, current_timestamp);
>
>
>
> CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
> integer, PassedPeriodID integer) RETURNS NUMERIC AS
> $FunctionCode$
> DECLARE
>   retval NUMERIC = 0.0 ;
> begin
>     return (SELECT
>       coalesce ( (select Sum(general_ledger_transactions.debit ) from
> general_ledger_transactions where general_ledger_transactions.coa_id =
> coa.coa_id and general_ledger_transactions.accounting_period =
> PassedPeriodID), 0 ) +
>       (CASE WHEN coa.doIhaveChildren THEN
>           GetChildAccountDebits(coa.coa_id, PassedPeriodID )
>       ELSE
>                  0.0
>       END)
>   FROM coa
>      WHERE  coa.parent_id = PassedAccountID);
>  end;
> $FunctionCode$
> LANGUAGE 'plpgsql' VOLATILE ;
>
> select 10, getchildaccountdebits(10,1)
> union
> select 11, getchildaccountdebits(11,1)
> union
> select 12, getchildaccountdebits(12,1);
>
>
> --------------------------------------------------
>
> WaGathoni wrote:
>>
>> Justin was recommending a solution to the Chart of Accounts Problem
>> posted by jamhitz:
>>
>> MQUOTE>
>> One has you chart of Accounts
>>   Create table coa (
>>      coa_id serial not null,
>>      parent_id int not null default 0,
>>      doIhaveChildren boolean default false
>>       account_name text null )
>> primary key(coa_id)
>>
>> Create Table general_ledger_transactions(
>>      transaction_id serial not null
>>      coad_id integer,
>>      accounting_period integer,
>>      debit numeric(20,10) ,
>>      credit numeric(20,10),
>>      transaction_date datestamp)
>> primary key (transaction_id)
>>
>> ...
>>
>> Create table  accounting_periods (
>>   accounting_period serial not null,
>>   start_date date,
>>   end_date date,
>>   accounting_period_Open boolean)
>>
>> </QUOTE>
>>
>> Would someone please assist me.  Why is the following function:...
>>
>>
>> CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
>> integer, PassedPeriodID integer) RETURNS NUMERIC AS
>> $FunctionCode$
>> DECLARE retval NUMERIC :=0.0;
>> begin
>>        SELECT Sum(gl_transactions.debit) +
>>                CASE WHEN coa.doIhaveChildren THEN
>>                        GetChildAccountDebits(coa.coa_id, PassedPeriodID )
>>                ELSE
>>                   0.0
>>                END
>>        INTO retval
>>        FROM gl_transactions, coa
>>        WHERE gl_transactions.coa_id= coa.coa_id
>>                AND coa.parent_id = PassedAccountID
>>                AND gl_transactions.period_id = PassedPeriodID;
>>
>>        RETURN retval;
>> end;
>> $FunctionCode$
>>  LANGUAGE 'plpgsql' VOLATILE ;
>>
>> ....failing with an error to the effect that that that
>> coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY
>> clause.... and what is is the recommended course of action.
>>
>> I have limited Internet access, so forgive me when I raise the same
>> question 8 days later.
>>
>> Thanks
>>
>>
>