CODEWITHSUNDEEP
sqloracle-databasecountconditional-statements
mkab
mkab

Reputation: 953

Oracle sql count() function?

I have a problem with the function count(). I want to it to count only under specific conditions.

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:

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

I want something of this format (without the where clause):

count(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)

Any ideas? Thanks.

Upvotes: 0

Views: 1968

Answers (6)

mkab
mkab

Reputation: 953

I found the query. It gives me the right table

SELECT ag.nom_agence as NOM_AGENCE, ag.responsable_agence as RESPONSABLE, count(case when to_char(l.date_location, 'YYYY') = '2010' and l.duree>3 then 1 end) as NOMBRE
FROM agences ag, locations l
WHERE ag.id_agence = l.id_agence
      group by nom_agence, responsable_agence

Thanks for your help, everyone.

Upvotes: -1

StevieG
StevieG

Reputation: 8729

You need to use an outer join to the locations table to make sure you always pull in all rentals in the period irrespective of their length.

Then count the number of rentals > 3 days.

Try this:

SELECT 
  NOM_AGENCE, 
  RESP_AGENCE,
  SUM(RESPONSABLE)
FROM 
  (
    SELECT 
      ag.nom_agence as NOM_AGENCE, 
      ag.responsable_agence RESP_AGENCE, 
      CASE  
        WHEN l.duree > 3 THEN 1
        ELSE 0
      END RESPONSABLE
    FROM 
      agences ag LEFT OUTER JOIN locations l ON 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') 
  ) 
GROUP BY 
  NOM_AGENCE,
  RESP_AGENCE

Upvotes: 3

vc 74
vc 74

Reputation: 38179

Try the following (I did not try it).

It's basically and outer join between the agencies table and the rentals grouped by agency following your search filter.

The NVL transforms the count for the agencies that don't have a match in the right query to a 0.

select left.id_agence, left.nom_agence, left.responsable_agence, NVL(right.count, 0)
from
(select id_agence, nom_agence, responsable_agence from agences) left
left outer join 
(
  SELECT id_agence, count(*) as count
  FROM locations
  WHERE date_location BETWEEN to_date('01/01/2010','DD.MM.YYYY') AND     to_date('31/12/2010','DD.MM.YYYY') AND
      duree > 3
  group by id_agence
) right
on left.id_agence = right.id_agence

Upvotes: 1

Dylan Smith
Dylan Smith

Reputation: 22255

SELECT ag.nom_agence as NOM_AGENCE, ag.responsable_agence, count(*) as RESPONSABLE
FROM agences ag LEFT OUTER JOIN locations l ON ag.id_agence = l.id_agence 
WHERE 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

The key is you need to do a LEFT OUTER JOIN which will retrieve all records from your agences table regardless of whether there are any matching records in locations

Upvotes: 0

p.matsinopoulos
p.matsinopoulos

Reputation: 7810

My quick answer is that you have to do LEFT JOIN and not INNER JOIN that you do. Try this:

SELECT ag.nom_agence as NOM_AGENCE, ag.responsable_agence, count(*) as RESPONSABLE
FROM agences ag
left join locations l on l.id_agence = ag.id_agence
WHERE 
  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

Upvotes: 0

JJS
JJS

Reputation: 1

Use the having clause:

having count(*) > 0

Upvotes: 0

Related Questions