Group by in DIVISION - Mailing list pgsql-bugs

hai,

     I am new to SQL...

        I am trying to get the result of some mathematical calculation with
group by some key.
I can get the result without grouping. here with i specified my table
information and the SQL i have used to get the result..

       can anybody help me to write the correct SQL..

In this i used 4 tables

/*  tdos table */
CREATE TABLE tdos
(
  dosid integer NOT NULL DEFAULT nextval('dos_sequence'::regclass),
  dcode character varying(20) NOT NULL,
  count integer NOT NULL,
  dosresult integer NOT NULL,
  standardcode character varying(12) NOT NULL,
  dosnakbn integer NOT NULL,
  dosintrsv1 integer,
  dosintrsv2 integer,
  dosdatersv1 date,
  dosdatersv2 date,
  dostxtrsv1 text,
  dostxtrsv2 text,
  CONSTRAINT tdos_pkey PRIMARY KEY (dosid),
  CONSTRAINT tdos_mstandard_fkey FOREIGN KEY (standardcode)
      REFERENCES mstandard (standardcode) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tdos_tdlr_fkey FOREIGN KEY (dlrcode)
      REFERENCES tdlr (dlrcode) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE tdos OWNER TO postgres;

/*mrea table*/
CREATE TABLE mrea
(
  arcode character varying(20) NOT NULL,
  aeregistdate date NOT NULL,
  arupdatedate date NOT NULL,
  arstartdate date,
  arenddate date,
  areaintrsv1 integer,
  areaintrsv2 integer,
  areadatersv1 date,
  areadatersv2 date,
  areatxtrsv1 text,
  areatxtrsv2 text,
  CONSTRAINT mrea_pkey PRIMARY KEY (arcode)
)
WITH (OIDS=FALSE);
ALTER TABLE mrea OWNER TO postgres;


/*mscn table*/
CREATE TABLE mscn
(
  scncode character varying(20) NOT NULL,
  arcode character varying(20) NOT NULL,
  oldscncode character varying(20) NOT NULL,
  dtkbn integer NOT NULL,
  inputfilepath text NOT NULL,
  outputfilepath text NOT NULL,
  backupfilepath text NOT NULL,
  nscstartdate date,
  nscenddate date,
  nscregistdate date NOT NULL,
  nscupdatedate date NOT NULL,
  nscintrsv1 integer,
  nscintrsv2 integer,
  nscdatersv1 date,
  nscdatersv2 date,
  nsctxtrsv1 text,
  nsctxtrsv2 text,
  CONSTRAINT mscn_pkey PRIMARY KEY (scncode),
  CONSTRAINT mkey_fkey FOREIGN KEY (arcode)
      REFERENCES mrea (arcode) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE mscn OWNER TO postgres;

/*mstandard table*/

CREATE TABLE mstandard
(
  standardcode character varying(12) NOT NULL,
  oldstandardtdcode character varying(12) NOT NULL,
  "level" character varying(3) NOT NULL,
  title text NOT NULL,
  ststartdate date,
  stenddate date,
  registdate date NOT NULL,
  updatedate date NOT NULL,
  stdintrsv1 integer,
  stdintrsv2 integer,
  stddatersv1 date,
  stddatersv2 date,
  stdtxtrsv1 text,
  stdtxtrsv2 text,
  CONSTRAINT mstandard_pkey PRIMARY KEY (standardcode)
)
WITH (OIDS=FALSE);
ALTER TABLE mstandard OWNER TO postgres;

SQL i have used:

select ((select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
mscn.scncode = 'AAAA' and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2006/11/1' and '2007/4/1')) as Periodone,
(select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
mscn.scncode = 'AAAA' and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1' ) as Periodtwo,
Round(((select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
mscn.scncode = 'AAAA' and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1') -
(select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
mscn.scncode = 'AAAA' and
tdos.dosdatersv1 between '2006/11/1' and '2007/4/1'))/(1.0*((select count(*)
from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
mscn.scncode = 'AAAA' and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1')))*100,0)
As Growthrate.

in this SQL i calculated the result only for one scncode.
i need this result for all scncode under mscn table.


Thanks in advance.
John.








--
View this message in context: http://www.nabble.com/Group-by-in-DIVISION-tp20769635p20769635.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.