CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

Having count in SELECT clause

Find the names of cities that hosts both SALES and TRANSPORT departments

For my oracle database have this table

i.) DEPTLOC

//DEPTLOC
CITY        DNAME
---------------------
NEWYORK       IT
NEWYORK     COMPUTER
LONDON      Science
LONDON      SALES
LONDON      TRANSPORT

For my SQL select statement

SELECT CITY FROM DEPTLOC
WHERE 
    DEPTLOC.DNAME='SALES' OR DEPTLOC.DNAME='TRANSPORT'
GROUP BY
CITY
HAVING COUNT(*)=2;

the output always display 

no rows selected.

My output should be

DNAME
--------
LONDON

Upvotes: 0

Views: 1412

Answers (3)

DRapp
DRapp

Reputation: 48139

For things like this, I try to use a simple join to the same table. First, I would have an index on the table by (City, DName).. then

select
      d.City
   from
      deptLoc d
         JOIN deptLoc d2
            on d.city = d2.city
           AND d2.dname = 'TRANSPORT'
   where
      d.dname = 'SALES'

It may look strange, but think about it. The outer portion WHERE clause only cares about cities that have ONE of the qualifiers. Why even count cities that dont even have that. So, now the join. Since you know the first qualifier on SALES is covered, re-join to the dept loc table again, but on the same city name AND the second instance is ALSO that of your 'TRANSPORT' component. You will be surprised at how fast it would be, especially on a large dataset.

Upvotes: 1

Siberia
Siberia

Reputation: 137

i think this could be a better query.

SELECT CITY 
FROM DEPTLOC
WHERE 
DEPTLOC.DNAME in ('SALES','TRANSPORT')
GROUP BY CITY

Upvotes: 0

CompEng
CompEng

Reputation: 7376

can you try this :

SELECT CITY FROM DEPTLOC
WHERE 
    trim(DEPTLOC.DNAME)='SALES' OR trim(DEPTLOC.DNAME)='TRANSPORT'
GROUP BY
CITY
HAVING COUNT(*)=2;

I think in your data you have some different chars like space or new lines

you can check it

Upvotes: 0

Related Questions