15583

SELECT count(*) then output empty or not empty with yes or no

Question:

Find if SALES department has its locations in exactly the same cities as TRANSPORT department"(assume that an empty result means YES and any nonempty result means NO)

In my database got this table.

//DEPTLOC DNAME CITY ---------------- SALES LONDON TRANSPORT LONDON TRANSPORT BOSTON SCIENCE BOSTON

Not sure how to write the query but at least i try

SELECT COUNT(*) FROM DEPTLOC WHERE (DNAME='SALES' AND DNAME='TRANSPORT') AND

How should i write after the AND operator in sql in order to get output something like this

//Display YES . (<--- LONDON , got SALES and TRANSPORT in same cities)

Answer1:

You can use <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm" rel="nofollow">decode</a>

SELECT Decode(Count(*), 0, 'No', 'Yes') FROM {rest of your query}

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

Answer2:

To pick up whether you have sales and transport you should use a self join.

By aliasing the DEPTLOC table as 'sales' and 'transport' and joining on the city name you will get a list of the city which have both sales & transport.

SELECT sales.CITY FROM DEPTLOC sales INNER JOIN DEPTLOC transport ON sales.CITY = transport.CITY WHERE sales.DNAME = 'SALES' AND transport.DNAME = 'TRANSPORT'

To get a list of yes and no you can use the above as a sub-query and use a case statement. I have added the distinct clause to eliminate any duplicate entries.

SELECT DISTINCT CITY, CASE WHEN salesandtransport.CITY IS NULL THEN 'NO' ELSE 'YES' END as DISPLAY FROM DEPTLOC LEFT JOIN (SELECT sales.CITY AS CITY FROM DEPTLOC sales INNER JOIN DEPTLOC transport ON sales.CITY = transport.CITY WHERE sales.DNAME = 'SALES' AND transport.DNAME = 'TRANSPORT') salesandtransport ON DEPTLOC.CITY = salesandtransport.CITY

Answer3:

There are many ways to approach this problem. The following uses full outer join to compare the two sets of cities. The idea is that any non-matching city will produce a NULL value. This is detecting by comparing count(*) to count(xx.City):

select (case when count(*) = count(dls.City) and count(*) = count(dlt.City) then 'Yes' else 'No' end) from DeptLoc dls full outer join DeptLoc dlt on dls.Dname = 'Sales' and dlt.Dname = 'Transport' and dls.City = dlt.City;

Answer4:

I think you should group by CITY and output Cities where count of DEPTS is not equal to count of DEPTS you need (2 in this case):

SELECT CASE WHEN EXISTS(SELECT CITY FROM DEPTLOC WHERE DNAME IN('SALES','TRANSPORT') GROUP BY CITY HAVING COUNT(*)<>2 ) THEN 'NO' ELSE 'YES' END FROM DUAL;

<kbd><a href="http://sqlfiddle.com/#!4/80033/1" rel="nofollow">SQL Fiddle demo</a></kbd>

Answer5:

This simple query should work just fine for you:

SELECT CITY,'YES' FROM DEPTLOC WHERE CITY IN(SELECT CITY FROM DEPTLOC WHERE DNAME='TRANSPORT') AND DNAME ='LOCATION'

OUTPUT will have those cities those have both transport and location and look like:

LONDON YES

Answer6:

I guess, this is what you are after:

SELECT CITY,COUNT(*),CASE WHEN COUNT(*)>2 THEN 'YES' ELSE 'NO' END AS STATUS FROM DEPTLOC WHERE (DNAME='SALES' OR DNAME='TRANSPORT') AND CITY='LONDON' GROUP BY CITY

Result:

CITY COUNT(*) STATUS LONDON 2 YES

See result in <a href="http://www.sqlfiddle.com/#!4/b2452/11" rel="nofollow"><strong>SQL Fiddle</strong></a>.

<strong>EXPLANATION:</strong>

STATUS field will be YES only if it returns 2 rows for that city. .i.e., 1 for Sales and 1 for Transport.

Answer7:

This question is a little bit ambiguous, but I now think that this is what is asked: Give 'YES' if for all cities where there is a SALES, there is also a TRANSPORT, and NO otherwise.

I believe this query should achieve this.

SELECT CASE WHEN Cnt = 0 THEN 'NO' ELSE 'YES' END AS Answer FROM (SELECT COUNT(*) AS Cnt FROM DUAL WHERE NOT EXISTS (SELECT * FROM DeptLoc dl WHERE dl.DName = 'SALES' AND NOT EXISTS (SELECT * FROM DeptLoc WHERE City = dl.City AND DName = 'TRANSPORT')));

Modified from SQL Fiddle by Raging Bull: <a href="http://www.sqlfiddle.com/#!4/ff7a8/1" rel="nofollow">http://www.sqlfiddle.com/#!4/ff7a8/1</a>

Answer8:

Here's another alternative. A FULL OUTER JOIN is made between the rows having DNAME as SALES and TRANSPORT. Their differences are found if there are any rows with CITY as NULL in the other set, as follows:

SELECT DECODE(COUNT(*), 0, 'YES', 'NO') Display FROM ( SELECT s.DNAME as s_DNAME, s.CITY as s_CITY, t.DNAME as t_DNAME, t.CITY as t_CITY FROM (SELECT DNAME, CITY FROM DEPTLOC WHERE DNAME = 'SALES') s FULL OUTER JOIN (SELECT DNAME, CITY FROM DEPTLOC WHERE DNAME = 'TRANSPORT' ) t ON s.CITY = t.CITY WHERE s.CITY IS NULL OR t.CITY IS NULL );

Here's the <strong><a href="http://www.sqlfiddle.com/#!4/dd8b8/39" rel="nofollow">SQL Fiddle</a></strong>.

The SQL Fiddle also contains a query to list the cities with only SALES or TRANSPORT.

Recommend

  • How to set up multiple aliased joins between two models?
  • Mvapich2 buffer aliasing
  • (How) am I supposed to destroy a uv_async_t?
  • White spots when drawing mutliple lines close to each other
  • Finding mutual friend sql
  • Lead() and LAG() functionality in SQL Server 2008
  • How could I rewrite a query without using joins
  • Google Play Games not allowing user to join turn-based match
  • Sending mail with ASP.NET
  • Python Beginner: How can I make text #1 look like text #2
  • Remote debugging of a Java application launched as a Windows service
  • How can I do a 301 redirect from http to https in Wildfly 8.2?
  • JPA - getting distinct value from one column
  • php regex remove digits
  • Add delivery info to query in SAP Crystal Reports
  • Update a record where _id = :id with Mongoose
  • Paramiko SSHException Channel Closed
  • reduce/reduce conflicts using ocamlyacc
  • How to write string.Contains(someText) in expression Tree
  • How to get the index of element in the List in c#
  • Rails AREL .where statement
  • LINQ join with filter criteria
  • How secure are apple APNS push notifications?
  • SQL getting multiple columns into one column
  • What's the syntax to inherit documentation from another indexer?
  • Why the SequenceFile is truncated?
  • DIV instruction jumping to random location?
  • Django: Count of Group Elements
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • How to get next/previous record number?
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Turn off referential integrity in Derby? is it possible?
  • LevelDB C iterator
  • Add sale price programmatically to product variations
  • How can i traverse a binary tree from right to left in java?
  • Unable to use reactive element in my shiny app
  • How do I use LINQ to get all the Items that have a particular SubItem?