Re: Consolidation of specific rows retaining another ones unaltered

From: Sameer <dolpheen_at_gmail.com>
Date: 24 Oct 2006 22:34:35 -0700
Message-ID: <1161754475.338392.191800@h48g2000cwc.googlegroups.com>

On Oct 23, 8:30 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Sameer" <dolph..._at_gmail.com> a écrit dans le message de news: 1161583581.942641.306..._at_k70g2000cwa.googlegroups.com...
> | Dear All,
> | Please have a look at this Google document.
> |http://docs.google.com/View?docid=ddqx8p3w_1c2szdk
> | I want to consolidate 3 rows (SAS-AP, SAS-AR, SAS-GL) into 1 row SAS by
> | summing them columnwise and retaining sum into SAS row.
> |
> | One probable way is to group by using substr function on first three
> | characters.
> |
> | SELECT togroup, sum(master), sum(TRANSACTIONs), sum(reports),
> | sum(miscallaneous)FROM (SELECT mr_issues_typewise.*,
> | SUBSTR(MODULE, 1, 3) AS togroup FROM mr_issues_typewise)GROUP
> | BY togroup
> |
> | But this will also group INT- rows which I do not expect. Also I want
> | to have module names in the output.
> |
> | How to do this? Please help.
> |
> | -Sameer
> |
>
> select decode(substr(module,1,3),'SAS','SAS',module) groupe,
> sum(...)
> from ...
> group by <same decode>
> /
>
> Regards
> Michel Cadot

Thanks Michael !
The query worked for me is:

SELECT togroup MODULE,

  SUM(master) master,
  SUM(transactions) transactions,
  SUM(reports) reports,
  SUM(miscallaneous) miscallaneous

FROM
  (SELECT mr_issues_typewise.*,

     decode(SUBSTR(MODULE, 1, 3), 'FAS', 'FAS', MODULE) AS
  togroup
   FROM mr_issues_typewise)
GROUP BY togroup

-Sameer Received on Wed Oct 25 2006 - 00:34:35 CDT