sql - Two counts in three tables -


i trying count 2 columns using following query:

select distinct [district],    count (distinct [student identifier statewide california])           '11-12 enrollment',   (select count (distinct incdtkey)  [dbo].[disciplinestudentfile1112] grdlvlkey in ('15', '01', '02', '03', '04',                          '05', '06', '07', '08', '09',                          '10', '11', '12', '18', '19')) total_incidents    dbo.ssid1112studentenrollmentrecords (nolock) inner join    [dbo].[schooldetail] on cdscode = dbo.ssid1112studentenrollmentrecords.cdsorig     [enrollstatcodeorig] '10' ,    [grade level code] in ('ps', 'kn', '01', '02', '03',                           '04', '05', '06', '07', '08',                           '09', '10', '11', '12', 'ue', 'us') group [district] order [district]

my results are:

district      11-12 enrollment  total_incidents ab unified          20662                896371 ce unified          5387                 896371 dr unified          526                  896371 fj unified          1506                 896371 kt unified          8415                 896371

i can't figure out how individual counts in total_incidents column instead of total 896371 count?

an easy approach correlate subquery outer query:

select distinct sd.district,   count ( distinct ser.[student identifier statewide california] ) [11-12 enrollment],   ( select count( distinct dsf.incdtkey ) dbo.disciplinestudentfile1112 dsf     dsf.grdlvlkey in ( '15', '01', '02', '03', '04', '05', '06', '07', '08', '09',        '10', '11', '12', '18', '19' ) , -- note additional condition here.       dsf.district = sd.district ) total_incidents dbo.ssid1112studentenrollmentrecords ser (nolock) inner join    dbo.schooldetail sd on sd.cdscode = ser.cdsorig ser.enrollstatcodeorig '10' ,    [grade level code] in ( 'ps', 'kn', '01', '02', '03', '04', '05', '06', '07', '08',    '09', '10', '11', '12', 'ue', 'us' ) group sd.district order sd.district

i have made assumptions table schemas. recommend when using joins supply alias each table , use alias on every reference avoid confusion.

an alternative solution use join disciplinestudentfile1112 , summarize results using group by.


Comments

Post a Comment

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

i force app's home screen portrait , other views landscape. apple rejects app if this? if not , how force first screen launch in portrait mode? , force other screens launch in landscape. this not duplicate question search every possible answer in website. i force app's home screen portrait , other views landscape. apple rejects app if this? no apple not reject app this. have lot of apps doesn't support orientation. forcing on 1 screen won't cause app reject. if not , how force first screen launch in portrait mode? , force other screens launch in landscape. can on write : -(nsuinteger)supportedinterfaceorientations; function.
Read more

php - HTTP_REFERER woes: How can I allow access to a specific page, only when a visitor has visited another specific page beforehand? -

php newbie here, struggling crazy head around how go implementing http_referer-based security feature woefully inept downloads system. basically, have file download pages have specific string appended end of url, specific string being: ?thanks if visible in url, users able download file situated on it. works great, it's easy 'unlock' page adding '?thanks' part onto url themselves. allowing users bypass clumsy security , access premium files ease. if possible, i'd add security measure whereby attempting access page containing string '?thanks' did not arrive page containing '?pending' redirected homepage. make perfect solution me. just clarity, typical pre-download process goes this: user visits download page: [website]/download/page/123/ user completes recaptcha, gets redirected here: [website]/download/page/123/?pending user fills in details, gets redirected here: [website]/download/page/123/?thanks user receives file long-...
Read more

java Extracting Zip file -

i'm looking way extract zip file. far have tried java.util.zip , org.apache.commons.compress, both gave corrupted output. basically, input zip file contain 1 single .doc file. java.util.zip: output corrupted. org.apache.commons.compress: output blank file, 2 mb size. so far commercial software winrar work perfectly. there java library make use of this? this method using java.util library: public void extractzipnative(file filezip) { zipinputstream zis; stringbuilder sb; try { zis = new zipinputstream(new fileinputstream(filezip)); zipentry ze = zis.getnextentry(); byte[] buffer = new byte[(int) ze.getsize()]; fileoutputstream fos = new fileoutputstream(this.tempfolderpath+ze.getname()); int len; while ((len=zis.read(buffer))>0) { fos.write(buffer); } fos.flush(); fos.close(); } catch (filenotfoundexception e) { // todo auto-generated catch block ...
Read more