Unanswered: Multiple Conditional counts in one query
Here's my dilemma, I am trying to do multiple conditional counts and have the results show up in one query. I can do any one of the conditional counts easily, but I'm not sure how to do more than one without restricting the dataset on account of the WHERE statement. Here is an example of the table
FYEAR SCODE SCRSTAT INTDATE SELSTAT
04 49 U 12DEC03 SUB
04 49 UU 12DEC03 NONE
04 38 UD 13NOV03 SURF
03 49 D
02 49 UU 15OCT01 SURF
01 49 DROP
01 49 U DROP
01 36 U 12OCT00 SUB
Essentially, from this data I want to return a table that looks like this for one particular SCODE (in this example 49 from the table above)
Here is the breakdown of how each column is calculated. Note, each column is for a given SCODE, in the example above 49
FYEAR is simply the FYEAR from the table
UPSCREENED is the count where SCRSTAT = U, UU, or UD
DROPS is the sum of the DROP entries in SCRSTAT and SELSTAT
INTERVIEWED is the COUNT where INTDATE != NULL
SELECTED is the COUNT where SELSTAT = SUB or SURF
As I mentioned earlier, I can write the SQL for any of the given columns relatively easily, but am not sure how to combine them into one result. I need one result as this will be going into a report format. Thank you for your help.
either use a temp table to place the individual rollups into or you can create a query that looks up the first value and use domain aggregates for the other values.
To use domain aggregates in the QBE window start a new field and Alias it. Then do the sum or count you need e.g.
dcount("[FieldName]","tablename","[Criteria Field] = 'something').
If it is a large dataset then use the temp table, the domain aggregates are a bit slow.
If you need to supply user entered criteria you could also do a union query, but if you do then you will need to create dummy fields for proper field placement in the union query (union queries require the same number of fields and names in the proper order).
I do have user entered data. Does this require that I start a UNION query? Or will the temp table method still work? The temp table method would be my preferred method, as I'm semi-familiar with that, and not so much familiar at all with union queries and the other method.
I'm currently trying to implement this by creating temporary tables but am apparently clueless as to the syntax. This is how I thought it was done:
PARAMETERS [Begin Year] Text ( 255 ), [End Year] Text ( 255 ), [School Code] Text ( 255 );
CREATE TEMPORARY TABLE UPSCREEN(
SELECT NEWDB.FYEAR AS FY, COUNT(NEWDB.SCRSTAT) AS UPSCREENED
WHERE (NEWDB.FYEAR between [Begin Year] and [End Year]) AND (NEWDB.SCRSTAT = "U" OR NEWDB.SCRSTAT = "UU" OR NEWDB.SCRSTAT = "DU") AND NEWDB.SCODE =[School Code]
GROUP BY NEWDB.FYEAR);
However, this query gives the error "INVALID SQL STATEMENT: EXPECT SELECT, UPDATE, INSERT, OR DELETE" and then highlights CREATE in my SQL VIEW window. What is the correct syntax?