By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,705 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,705 IT Pros & Developers. It's quick & easy.

Count variables in a single field then Sum results in a query

P: 1
As a beginer,Im trying to COUNT values in a single field where they meet the critria below., Then SUM then altogether.

Coursename,enddate1 and enddate2 are Request.QueryStrings form an .ASP web page which gnerates the query

The field is wish to SUM is "SERVICELEVEL"
I wish to COUNT the following
addressbook.ServiceLevel='Officer' +
addressbook.ServiceLevel='Senior Ranks' +
addressbook.ServiceLevel='Junior Ranks' = x

The critiria
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Courselist INNER JOIN (addressbook INNER JOIN Applications ON addressbook.PersonID = Applications.PersonID) ON Courselist.CourseID = Applications.CourseID
  3. WHERE addressbook.Service='RN' AND  AND(Applications.StatusCode='Passed' OR Applications.StatusCode='Failed')  AND courselist.CourseTitle=coursename AND courselist.EndDate BETWEEN enddate1 AND enddate2
Im a bit stummped
Sep 3 '08 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,754
Even after tidying up your SQL I still find no reference in there to [ServiceLevel].
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  3. FROM Courselist INNER JOIN 
  4.     (addressbook INNER JOIN 
  5.      Applications
  6.   ON addressbook.PersonID = Applications.PersonID)
  7.   ON Courselist.CourseID = Applications.CourseID
  9. WHERE addressbook.Service='RN'
  10.   AND (Applications.StatusCode='Passed'
  11.    OR Applications.StatusCode='Failed')
  12.   AND courselist.CourseTitle=coursename
  13.   AND courselist.EndDate BETWEEN enddate1 AND enddate2
This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively.
Table Name=[tblStudent]
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Sep 7 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.