Oracle sql count() function?

Question

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.

StevieG · Accepted Answer · 2011-10-28 16:41:08Z

3

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

edited Oct 28, 2011 at 16:41

answered Oct 28, 2011 at 15:58

StevieG

8,7391 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mkab Over a year ago

l.duree > 3 isn't filtering it out. Actually I need it to satisfy the condition the number of rentals exceeding three days. But the WHERE clause is limiting the results

StevieG Over a year ago

try my latest edit, should work now - I was a bit confused by the original query

mkab Over a year ago

It didn't really work (error:missing keyword) but it gave me an idea on how to use case.

StevieG Over a year ago

sorry, cut and paste error, I had left a count(*) in after else 0 which shouldn't have been there.. does it work now?

mkab Over a year ago

There's a problem with the group by clause. agences ag is defined in the brackets so ag.nom_agence and ag.nom_responsble is undefined

|

vc 74 · Accepted Answer · 2011-10-28 16:16:17Z

1

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

answered Oct 28, 2011 at 16:16

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

2 Comments

mkab Over a year ago

It works but i don't understand the NVL clause. Seems to advanced for me lol. I just started learning Oracle sql. Thanks though :)

vc 74 Over a year ago

@mkab, NVL simply substitutes a value by another if the value is null. For instance, NVL(3, 4) will return 3 as 3 is not null but NVL(NULL, 12) will return 12. As outer joins produce null values where the datasets value don't match, NVL allows us to treat them as 0.

JJS · Accepted Answer · 2011-10-28 16:00:07Z

0

Use the having clause:

having count(*) > 0

answered Oct 28, 2011 at 16:00

JJS

1

Comments

p.matsinopoulos · Accepted Answer · 2011-10-28 16:00:22Z

0

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

answered Oct 28, 2011 at 16:00

p.matsinopoulos

7,8426 gold badges48 silver badges99 bronze badges

1 Comment

mkab Over a year ago

Same problem. The WHERE clause is limiting the results. It gives me the same table as the inner join

Dylan Smith · Accepted Answer · 2011-10-28 16:02:09Z

0

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

answered Oct 28, 2011 at 16:02

Dylan Smith

22.3k2 gold badges51 silver badges63 bronze badges

1 Comment

mkab Over a year ago

Same problem. The WHERE clause is limiting the results. It gives me the same table as the inner join

mkab · Accepted Answer · 2011-10-29 01:58:38Z

-1

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.

answered Oct 29, 2011 at 1:58

mkab

9535 gold badges16 silver badges31 bronze badges

Collectives™ on Stack Overflow

Oracle sql count() function?

6 Answers 6

6 Comments

2 Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

2 Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related