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/>
