We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Practice
  2. SQL
  3. Basic Select
  4. Weather Observation Station 4
  5. Discussions

Weather Observation Station 4

Sort 535 Discussions, By:

votes

  • aswinramakrish + 18 comments

    This is a terribly worded question. Here is how it should have been phrased -

    Write a query to find the difference between the total number of cities and the unique number of cities in the table STATION.

    I don't understand why he says "Let's say" and gives all NUM etc.

    • zonender + 3 comments

      I think they wanted us to use variables, since some solutions can be reached without variables.

      • noddy007jeet + 4 comments

        hey , how do i display the variables in oracle?

        • cricketcrazyray1 + 15 comments

          select count (city) - count(distinct city) from station;

        • samircity + 2 comments

          Oracle with Variable !

          variable v_count_distinct number;

          exec select count(CITY) - count(distinct CITY) into :v_count_distinct from STATION;

          print v_count_distinct;

        • gitwithravish + 0 comments

          select (count(city) - count(distinct city)) from station;

      • shravankumarH + 0 comments

        SELECT (COUNT(CITY) - COUNT(DISTINCT CITY)) FROM STATION;

      • dipti_anjarlekar + 0 comments

        select (select count(city) from station) - (select count(distinct city) from station) from dual ;

    • djgorman + 0 comments

      I agree - the question starts off asking for the number of city names that aren't unique. That is, the difference between (NUM) = the total # of city names is the # of distinct city names in the data set, and NUM{unique} = the # of city names that are used only once.

      Yet other parts of the question asks for something else: the # of city entries that are using a city name beyond the first time, which is the total # of city entries (not names) less the total # of city names.

      The difference manifests when there are more than 2 entries that use the same name. Given the data set:

      (ID, NAME) = (1, A) (2, C) (3, B) (4, C) (5, B) (6, C) (7, C)

      The first "non-unique city names" ask yields 2: there are 3 city names (A,B,C) and only 1 is "unique" (A).

      The entries ask yields 4: 7 entries less 3 names. It's 2 more than the other because (C) is used that many more times. I frankly don't find this ask very useful.

    • amruthak11 + 1 comment

      Thanks.After reading your rephrased question, I could submit correct answer.

      • ayushdubey1217 + 1 comment

        will you please provide the solution?

        • Deepusri + 1 comment

          select (count(CITY)-COUNT(DISTINCT CITY)) from STATION;

  • RPoonia + 0 comments

    Here problem statement itself is a problem.

  • rasheed_cse + 4 comments

    select (count(CITY)-COUNT(DISTINCT CITY)) from STATION;

    • reddy_raviteja46 + 2 comments

      but its showing Wrong Answer any idea ?

      • afig23 + 0 comments

        Check your parentheses. Othwerwise, this worked for me (same as rohitagni1993's solution): SELECT COUNT(City) - COUNT(DISTINCT City) FROM Station;

      • Gennarov94 + 0 comments

        In Oracle DB: select (select count( * ) from station)-(select count( * ) from(select distinct city from station)) from dual;

    • ayushchauhan_ + 2 comments

      Why this doesn't work ::: select (count(CITY)-count(DISTINCT CITY)) from STATION;

      • er_shreyansh1991 + 1 comment

        try to submit multiple time untill it shows successfull.

        • ajlrkind + 0 comments

          Useless

      • navidcs + 0 comments

        It works in MS SQL SERVER; Not the rest (MySql, DB2, and Oracle).

    • abdul_rashid + 0 comments

      I tried the same

  • RohitKumar_India + 0 comments
    SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;

    worked for me.

  • patil44 + 6 comments

    select count(city) - count (distinct city) from station

    This is the correct one......

    • pranjaliraiAsked to answer + 1 comment

      put distinct city as distinct(city)

      • maximshen + 2 comments

        Why ? Any reason for good practice ? Just curious.

        • AmoghM + 0 comments
          [deleted]

        • Koshux + 0 comments

          It appears that it works as a "function" just as COUNT does. It is not a keyword like SELECT but an operator that will sift through rows.

    • iqbalwasim + 1 comment

      thanks bro ..

      • mahesh1993 + 0 comments

        thanks

    • sarathn2013 + 0 comments

      I think question is asked for count of non-unique.

Load more conversations