CODEWITHSUNDEEP
sqloracle-database
swhit
swhit

Reputation: 51

Oracle SQL Exclude row from query results

I have two tables in Oracle SQL:

PROJECT (PID, Pname, Budget, DID)

DIVISION (DID, Dname)

Bold = Primary key

Italic = Foreign key

I want to list the division that has more projects than the division marketing.

Here is my code:

select dname as "Division"
from division d, project p
where d.did = p.did
group by dname
having count(pid) >= all
(select count(p.pid)
from project p, division d
where p.did = d.did and d.dname = 'marketing')

I return the correct record but also the marketing record. How can I exclude the marketing record from the results?

Upvotes: 1

Views: 4335

Answers (2)

glh
glh

Reputation: 4972

Why don't you exclude the marketing record from your initial SQL by adding:

and d.dname != 'marketing'

To the first where clause.

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52336

You might gain efficiency with a common table expression (WITH clause) to aggregate the count by department, then you can query it ...

with cte as (
  select   dname,
           count(*) projects
  from     project p,
           division d
  where    p.did = d.did
  group by dname)
select dname,
       projects
from   cte
where  projects > (select projects
                  from   cte
                  where  dname = 'Marketing)

Upvotes: 0

Related Questions