DIVISION with Group By - Mailing list pgsql-sql

Hai all,<br /><br />I am new to SQL coding.<br />I need some help in writing SQL<br />In my task i have to calculate
divisionof two sub queries.<br />each sub query has its own group by clause.<br /><br />Here with i have Paste my table
informationand my SQL.<br /><br /><br /><span class="ul-threaded" style="margin: 1em 0pt 0pt -20px;"><span
class="connect"><spanclass="li-threaded" id="li1"><span class="threaded"><span class="no-bg-color post-border
post-middle"style="position: static; border-left-width: 3px;"><span id="post20769635" style="display: block;">/*  tdos
table*/ <br />CREATE TABLE tdos <br />( <br />  dosid integer NOT NULL DEFAULT nextval('dos_sequence'::regclass), <br
/> dcode character varying(20) NOT NULL, <br />  count integer NOT NULL, <br />  dosresult integer NOT NULL, <br /> 
standardcodecharacter varying(12) NOT NULL, <br />  dosnakbn integer NOT NULL, <br />  dosintrsv1 integer, <br /> 
dosintrsv2integer, <br />  dosdatersv1 date, <br />  dosdatersv2 date, <br />  dostxtrsv1 text, <br />  dostxtrsv2
text,<br />  CONSTRAINT tdos_pkey PRIMARY KEY (dosid), <br />  CONSTRAINT tdos_mstandard_fkey FOREIGN KEY
(standardcode)<br />      REFERENCES mstandard (standardcode) MATCH SIMPLE <br />      ON UPDATE NO ACTION ON DELETE NO
ACTION,<br />  CONSTRAINT tdos_tdlr_fkey FOREIGN KEY (dlrcode) <br />      REFERENCES tdlr (dlrcode) MATCH SIMPLE <br
/>     ON UPDATE NO ACTION ON DELETE NO ACTION <br />) <br />WITH (OIDS=FALSE); <br />ALTER TABLE tdos OWNER TO
postgres;<br /><br />/*mrea table*/ <br />CREATE TABLE mrea <br />( <br />  arcode character varying(20) NOT NULL, <br
/> aeregistdate date NOT NULL, <br />  arupdatedate date NOT NULL, <br />  arstartdate date, <br />  arenddate date,
<br/>  areaintrsv1 integer, <br />  areaintrsv2 integer, <br />  areadatersv1 date, <br />  areadatersv2 date, <br /> 
areatxtrsv1text, <br />  areatxtrsv2 text, <br />  CONSTRAINT mrea_pkey PRIMARY KEY (arcode) <br />) <br />WITH
(OIDS=FALSE);<br />ALTER TABLE mrea OWNER TO postgres; <br /><br /><br />/*mscn table*/ <br />CREATE TABLE mscn <br />(
<br/>  scncode character varying(20) NOT NULL, <br />  arcode character varying(20) NOT NULL, <br />  oldscncode
charactervarying(20) NOT NULL, <br />  dtkbn integer NOT NULL, <br />  inputfilepath text NOT NULL, <br /> 
outputfilepathtext NOT NULL, <br />  backupfilepath text NOT NULL, <br />  nscstartdate date, <br />  nscenddate date,
<br/>  nscregistdate date NOT NULL, <br />  nscupdatedate date NOT NULL, <br />  nscintrsv1 integer, <br />  nscintrsv2
integer,<br />  nscdatersv1 date, <br />  nscdatersv2 date, <br />  nsctxtrsv1 text, <br />  nsctxtrsv2 text, <br /> 
CONSTRAINTmscn_pkey PRIMARY KEY (scncode), <br />  CONSTRAINT mkey_fkey FOREIGN KEY (arcode) <br />      REFERENCES
mrea(arcode) MATCH SIMPLE <br />      ON UPDATE NO ACTION ON DELETE NO ACTION <br />) <br />WITH (OIDS=FALSE); <br
/>ALTERTABLE mscn OWNER TO postgres; <br /><br />/*mstandard table*/ <br /><br />CREATE TABLE mstandard <br />( <br /> 
standardcodecharacter varying(12) NOT NULL, <br />  oldstandardtdcode character varying(12) NOT NULL, <br />  "level"
charactervarying(3) NOT NULL, <br />  title text NOT NULL, <br />  ststartdate date, <br />  stenddate date, <br /> 
registdatedate NOT NULL, <br />  updatedate date NOT NULL, <br />  stdintrsv1 integer, <br />  stdintrsv2 integer, <br
/> stddatersv1 date, <br />  stddatersv2 date, <br />  stdtxtrsv1 text, <br />  stdtxtrsv2 text, <br />  CONSTRAINT
mstandard_pkeyPRIMARY KEY (standardcode) <br />) <br />WITH (OIDS=FALSE); <br />ALTER TABLE mstandard OWNER TO
postgres;<br /><br />SQL i have used: <br /><br />select ((select count(*) from tdos,tdlr,mscn,mstandard <br />where
tdos.standardcode= mstandard.standardcode and <br />tdos.dlrcode=tdlr.dlrcode and <br />tdlr.scncode = mscn.scncode and
<br/>tdos.dosresult = 1 and <br />mscn.scncode = 'AAAA' and <br />tdos.dosnakbn = 0 and <br />tdos.dosdatersv1 between
'2006/11/1'and '2007/4/1')) as Periodone, <br />(select count(*) from tdos,tdlr,mscn,mstandard <br />where
tdos.standardcode= mstandard.standardcode and <br />tdos.dlrcode=tdlr.dlrcode and <br />tdlr.scncode = mscn.scncode and
<br/>mscn.scncode = 'AAAA' and <br />tdos.dosresult = 1 and <br />tdos.dosnakbn = 0 and <br />tdos.dosdatersv1 between
'2007/4/1'and '2008/11/1' ) as Periodtwo, <br />Round(((select count(*) from tdos,tdlr,mscn,mstandard <br />where
tdos.standardcode= mstandard.standardcode and <br />tdos.dlrcode=tdlr.dlrcode and <br />tdlr.scncode = mscn.scncode and
<br/>mscn.scncode = 'AAAA' and <br />tdos.dosresult = 1 and <br />tdos.dosnakbn = 0 and <br />tdos.dosdatersv1 between
'2007/4/1'and '2008/11/1') - <br />(select count(*) from tdos,tdlr,mscn,mstandard <br />where tdos.standardcode =
mstandard.standardcodeand <br />tdos.dlrcode=tdlr.dlrcode and <br />tdlr.scncode = mscn.scncode and <br
/>tdos.dosresult= 1 and <br />tdos.dosnakbn = 0 and <br />mscn.scncode = 'AAAA' and <br />tdos.dosdatersv1 between
'2006/11/1'and '2007/4/1'))/(1.0*((select count(*) from tdos,tdlr,mscn,mstandard <br />where tdos.standardcode =
mstandard.standardcodeand <br />tdos.dlrcode=tdlr.dlrcode and <br />tdlr.scncode = mscn.scncode and <br
/>tdos.dosresult= 1 and <br />tdos.dosnakbn = 0 and <br />mscn.scncode = 'AAAA' and <br />tdos.dosdatersv1 between
'2007/4/1'and '2008/11/1')))*100,0) <br />As Growthrate. <br /><br />in this SQL i calculated the result only for one
scncode.<br />i need this result for all scncode under mscn table. <br /><br /><br />If you have other logic to
calculatethis,please tell me the logic<br /><br /><br />Thanks in advance. <br />John. <br
/></span></span></span></span></span></span><br/>