Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Count() with a condition

    Hello everyone.

    I have a problem with the function count().

    What i want to do is to display for each company, the company's name, the name of the company's boss and the number of rentals exceeding three days made in 2010.

    So the condition is: the number of rentals exceeding three days made in 2010.

    Therefore, if the company doesn't have any rentals that satisfy the condition, it shouldn't be eliminated form the resulting table but instead it should be written zero. For example:


    company 1 -------------------- BOSS 1-----------------------2
    company 2---------------------- BOSS 2---------------------- 0 --doesn't satisfy the condition: 0 rentals
    company 3-----------------------BOSS 3 ----------------------5
    company 4---------------------- BOSS 4--------------------------1
    company 4 ----------------------BOSS 5 ----------------------- 0 --doesn't satisfy the condition: 0 rentals

    AND NOT

    company 1----------------------BOSS 1---------------------------2
    company 3--------------------- BOSS 3---------------------------5
    company 4----------------------BOSS 4 --------------------------1

    My sql codes displays the second table and not the first table. This is my code:

    Code:
    SELECT ag.nom_agence as NOM_AGENCE, ag.responsable_agence, count(*) as RESPONSABLE
    FROM agences ag, locations l
    WHERE ag.id_agence = l.id_agence AND
          l.date_location BETWEEN to_date('01/01/2010','DD.MM.YYYY') AND to_date('31/12/2010','DD.MM.YYYY') AND
          l.duree > 3
          group by ag.nom_agence,ag.responsable_agence
    Any ideas please? Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any ideas please?
    DECODE()
    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
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Select sum(decode(least(l.duree,3),3,0,1)) number_over_three
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by mkab View Post
    Any ideas please? Thanks
    Looks to me like simple outer join. See some examples here: http://psoug.org/reference/joins.html

Tags for this Thread

Posting Permissions

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