Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    8

    Unanswered: Problem in Calculating count?

    hi once again,
    i am not sure i have to use count function or not , if yes how?
    okay here is my database fields

    Drivers: DriverId,FirstName,LastName,Age,Seats
    Visitors:VisitorId,GroupId,FirstName,LastName,Age
    Rides:driverId,GroupId,fromDate,todate
    Groups:groupid,Country
    Vlang(VisitorsLanguage):Visitorsid,language
    Dlang(Driverlanguage)riverId,Language

    i want to get the firstname and lastname of drivers that will be able to communicate with atleast half of the visitors they chauffer?

    Any help will be appreciated as i am completly stuck i am able to get the names but not able to figure out how to calculate the rest?
    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I see you once again expect others to do your homework assignment for you.

    Post DDL for tables
    Post DML for test data.
    Post SQL you've tried.
    Post expected/desired results.
    Last edited by anacedent; 02-22-09 at 14:56.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2009
    Posts
    8
    i used
    Select d.firstname,dl.language as Driverlang,v.firstname,v.lastname,vl.Language as visitorlang,r.groupid from drivers d,groups g,vlangs vl,rides r,dlangs dl,visitors v where dl.driverid=d.driverid and vl.visitorid=v.visitorid and v.groupid=r.groupid and r.groupid=g.groupid and r.driverid=d.driverid and vl.language=dl.language order by d.firstname
    this gives me
    FIRSTNAME DLANG FIRSTNAME LASTNAME VLANG GROUPID
    ---------- ---------- ---------- ---------- ---------- ----------
    Alexander Chinese Henry Dipper Chinese 6
    Ali English Issac Johnson English 3
    Ali German Thomas Rogers German 3
    Ali English James Parrot English 2
    Ali English Thomas Rogers English 3
    Ali English Edward Robinson English 2
    Charles English Dennis Topping English 1
    James Japanese William Main Japanese 1
    James English Dennis Topping English 1
    Philip French Henry Dipper French 6
    Philip French John Sparkes French 6

    FIRSTNAME DLANG FIRSTNAME LASTNAME VLANG GROUPID
    ---------- ---------- ---------- ---------- ---------- ----------
    Roger Spanish Peter Harris Spanish 7
    Roger Spanish Francis Friggs Spanish 7

    now i want to know that which driver can cumunicate more than half of his visitors?

  4. #4
    Join Date
    Feb 2009
    Posts
    8
    i know to use max will return me the max appearance but i am not sure am i right or not?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i know to use max will return me the max appearance
    What is "max appearance" & what does it have to do with this problem?

    Code:
    SELECT   d.firstname, 
             dl.language AS driverlang, 
             v.firstname, 
             v.lastname, 
             vl.language AS visitorlang, 
             r.groupid 
    FROM     drivers d, 
             GROUPS g, 
             vlangs vl, 
             rides r, 
             dlangs dl, 
             visitors v 
    WHERE    dl.driverid = d.driverid 
             AND vl.visitorid = v.visitorid 
             AND v.groupid = r.groupid 
             AND r.groupid = g.groupid 
             AND r.driverid = d.driverid 
             AND vl.language = dl.language 
    ORDER BY d.firstname
    How many visitors have each driver serviced?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Feb 2009
    Posts
    8
    "How many visitors have each driver serviced?"

    NO, i want to know that which driver can cumunicate with more than half of his visitors

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >"How many visitors have each driver serviced?"
    >NO, i want to know that which driver can cumunicate with more than half of his visitors

    You need to start simple, because this whole problem challenges you.

    Before you can determine "more than half", you need to know how many total so you can divide it by 2 and then add 1 (to compute more than half).

    If you know better & I am wrong, you are free to solve this on your own.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Feb 2009
    Posts
    8
    First, thats what i have done ..
    Second, once you figure out (by count) who is appeared most than its straight forward too the one who is appeard more he is the one who can communicate with more as vl.language=dl.language

    Thanks...

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >First, thats what i have done .
    If you say so.
    Produce COUNT by driver.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Feb 2009
    Posts
    8
    have done ...

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >have done ...
    If you say so, then proceed to solution.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •