Re: Do While not Statement

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/05/04 

Date: Wed, 5 May 2004 14:08:26 -0400

Depending on the database and version, you can probably do this in one
query, returning one row, and eliminating the need to loop at all, never
mind multiple times.

For SQL Server:

SELECT
    male = SUM(CASE gender WHEN 'male' THEN 1 ELSE 0 END),
    female = SUM(CASE gender WHEN 'female' THEN 1 ELSE 0 END),
    basePaper = SUM(CASE referral WHEN 'Base Paper' THEN 1 ELSE 0 END)
FROM tblUsers

I'm continually amazed that people think it is too much work, or not
valuable enough, to include what type of database they are working with... 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OW1eTnsMEHA.1388@TK2MSFTNGP09.phx.gbl...
> How about just doing a count query or queries?
>
>
> intMale = odoCon.Execute("SELECT COUNT(gender) FROM tblUsers WHERE
> gender='male'").Fields.Item(0).Value
> intFemale = odoCon.Execute("SELECT COUNT(gender) FROM tblUsers WHERE
> gender='female'").Fields.Item(0).Value
> intBasePaper = odoCon.Execute("SELECT COUNT(referral) FROM tblUsers WHERE
> referral='Base Paper'").Fields.Item(0).Value
>
> (Some would argue against the methods above, however.)
>
> For sexes, you could also do "SELECT gender,COUNT(gender) FROM tblUsers
> GROUP BY gender ORDER BY gender" to gram male and female at once.
>
> Ray at work
>
>
>
>
> "Ben" <Ben.DunlapHAHA@langley.af.mil> wrote in message
> news:%23jImA9rMEHA.3572@tk2msftngp13.phx.gbl...
> > I have two 'Do While Not' statements, that are getting information from
> > the same recordset. If I comment out the first one I can get the results
> > for the second one, and vice-versa. Why is this happening? Is it because
> > it's already at the EOF? If so how do I get it back to the BOF for the
> > 2nd 'Do While Not' statement?
> >
> > '----------------------------------------
> > 'Create an ADO recordset object
> > Set rs_Report = Server.CreateObject("ADODB.Recordset")
> >
> > 'Initialise the strSQL variable with an SQL statement to query the
> > database
> > strSQL = "SELECT gender, referral, youthmember, age FROM tblUsers WHERE
> > base = '" + Replace(chosenBase, "'", "''") + "'"
> >
> > 'Open the recordset with the SQL query
> > rs_Report.Open strSQL, adoCon
> >
> > '-----Gets the total number of male and female users
> >  intMale = 0
> >  intFemale = 0
> >  Do While NOT rs_Report.EOF
> >    If rs_Report("gender") = "male" then
> >       intMale = intMale + 1
> >    Else
> >       intFemale = intFemale + 1
> >    End If
> >  rs_Report.MoveNext()
> >  Loop
> > '-----Figures out how they heard about the program
> >
> >  Do While NOT rs_Report.EOF
> >    If rs_Report("referral") = "Base Paper" then
> >       intBasePaper = intBasePaper + 1
> >    End IF
> >  rs_Report.MoveNext()
> >  Loop
> > ----------------------------------------------
> > --Sample Output--
> >  Male = 11
> >  Female = 4
> >
> >  Base Paper =__   "nothing is returned here"
> >
> > ----------------------------------------------
> > Like I said before If I comment out the first do while not statement I
> > can get the answers for the second one. If I don't it doesn't even get
> > inside of the second do while not statement.
> >
> > I know I can fix it by just having a seperate SQL statement to the
> > database and get my information seperately, but it seems like a waste of
> > time. Thanks for any help.
> >
> > -=Ben
> > -=To email me take out the joke.
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
>

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: complex filter and calculations in access
    ... switch to SQL view. ... query by switching to datasheet view, ... of your database using the from address in this post. ... pre-op infections yes/no ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ... for a s/w developer and a s/w developer simplifies for the end user). ...
    (comp.databases.pick)
  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... said data includes lists." ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ...
    (comp.databases.pick)