aswinramakrish 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 I think they wanted us to use variables, since some solutions can be reached without variables.
noddy007jeet hey , how do i display the variables in oracle?
cricketcrazyray1 select count (city) - count(distinct city) from station;
samircity Nice , I made it as below :
DECLARE @distinctdifferent VARCHAR(MAX)
SET @distinctdifferent = 'Select count(CITY) - count(distinct CITY) from STATION'
EXEC(@distinctdifferent)
nupoor_1013 this does not work
lahanemangesh1 Try this SELECT COUNT(CITY) - COUNT(DISTINCT CITY) AS N FROM STATION
andy17 Thanks,it worked.
stephaniie This got to be the most dreadful worded question I ever seen. Considered the poor quality in writing he could more likely used the childish language he attempts to show us..
Instead of "Let N be the number of CITY entries in STATION, and let N be the number of distinct CITY names in STATION; query the value of N - N from STATION."
Write: If I have these five items in a list: Monkey, Monkey, Lion, Tiger, Horse. How can I with SQL find out the number of unique items in the list?
Is there a feature for Us to Demand a Re-phrase of Questions in Hackerrank? Because after this.. I really hope there is one.
melindaburns24 It didn't work for me. What server did you use?
vora_raj71 Mysql
priyankerrao it's N' not N.
rohanshahone Why have you added "AS N"? WHat does it do?
shiv040998 it names the column(which shows the difference between the cites and the unique cities) AS n
vora_raj71 [deleted]vora_raj71 could you explain in more detail, please. I am asking this because it worked when I dint add asN in my query.
ganorkaryogita31 It is as like a alise for query, it means gives single word to the query;
kumardora16 Just Created an ALIAS for the given column name
bhuvithabhuvi22 what does AS N means can u explain?
yashbadia19 the column will be named as N and that column will hold the final value of (count(city)- count(distinct city)) i.e. 13.
you can simply do like this also
SELECT COUNT(CITY)-COUNT(DISTINCT CITY) FROM STATION ;
chandanasree35 we can also use UNIQUE insterd of DISTINCT
ayushuser1 IT IS ALIAS NAME, MEANS WE ARE GIVING A NAME TO THAT DATA WHICH WILL BE RETURNED AFTER SUBSTRACTING. i THINK THE VARIABLE NAME WHICH WE GIVE AS ALIAS CANNOT BE OPERATED AS IT IS JUST A LOGICAL NAME GIVEN FOR EASY UNDERSTANDING.
Ravibharathi as is used to alias name. we can display old name as new name.it will used to display only. it won't affect old name
Quazar777 why did you include 'as N' ? I don't think that's required.
ogundejiseyi I don't believe so either. I used the below which worked in Oracle
SELECT COUNT (CITY) - COUNT(DISTINCT(CITY)) FROM STATION;
ayushuser1 ITS NOT REQUIRED
harshrd WHY 'AS N' ?
bhavesh_shah like really do we need 'AS N'? SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION worked for me.
NtaoMoloi No we don't need it, It's useful in SQL Server when you want to display the results of your query in a new column.
rohitpr9852 WHY USE AS N
AKSHAY97 IF YOU DONT WRITE "AS N" IT WILL GIVE THE SAME OUTPUT.
jfbsog SELECT COUNT(*) - COUNT(DISTINCT CITY) from STATION
jcarroll1 Why on earth would you do this? There is no reason to use dynamic SQL in this example.
thelight_mn thank you man.
RUBEY123AT why is there a '( - )' between Select count(CITY) - count(distinct CITY)?
knightofcode the question's answer is the difference between number of CITY rows and 'distinct' CITY rows. so to calculate the difference we use -.
mkumavat7 [deleted]
palash_rawat It will first count total city entries in the table then it will subtract(-) the unique or distinct cities present in the table.
arpitkanchan94 It worked
Krishnegowdakv i tried this but it's not working for me!
Barsdha this doesnot run
susheel_pandey21 Thanks
sinyor VERY NICE ANSWER BRO
fbadaoui19 [deleted]rnovoa2014 Bravo!
samircity 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;
djgorman 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 Thanks.After reading your rephrased question, I could submit correct answer.
ayushdubey1217 will you please provide the solution?
Deepusri select (count(CITY)-COUNT(DISTINCT CITY)) from STATION;
mahes302 it is work or not
johnty05 select count(city)-count(distinct city) from Station;
cs_ankitprajapa1 it worked!
triniseanf Thank you for saying this could not for the life of me understand the question.
triniseanf [deleted]
c1b3rh4ck I agree with your statement, I was not able to submit until I reread the question.
BansheeGhoul SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;
shailesh_singh2 Many questions on Hackerrank are be written properly. Sucks trying to practice for job interview if questions don't make sense.
Here's how I solved this using MySQL: SELECT COUNT(city) - COUNT(distinct city) from STATION
srivastava_ramc1 Its not working for Mysql
kartik_madnani In a way it can also be said as how many cities are there with duplicate entries.
jfbsog SELECT COUNT(*) - COUNT(DISTINCT CITY) from STATION
RPoonia Here problem statement itself is a problem.
rasheed_cse select (count(CITY)-COUNT(DISTINCT CITY)) from STATION;
reddy_raviteja46 but its showing Wrong Answer any idea ?
afig23 Check your parentheses. Othwerwise, this worked for me (same as rohitagni1993's solution): SELECT COUNT(City) - COUNT(DISTINCT City) FROM Station;
Gennarov94 In Oracle DB: select (select count( * ) from station)-(select count( * ) from(select distinct city from station)) from dual;
ayushchauhan_ Why this doesn't work ::: select (count(CITY)-count(DISTINCT CITY)) from STATION;
er_shreyansh1991 try to submit multiple time untill it shows successfull.
navidcs It works in MS SQL SERVER; Not the rest (MySql, DB2, and Oracle).
abdul_rashid I tried the same
chandan_kapoor72 why did we use a capital 'count' for distinct city instead for normal count
RajaRaghav because 'MAGIC'
2016_priyanka_t1 Sql is not case sensitive so don't worry we can use capital or small letters.
patil44 select count(city) - count (distinct city) from station
This is the correct one......
pranjaliraiAsked to answer put distinct city as distinct(city)
iqbalwasim thanks bro ..
mahesh1993 thanks
sarathn2013 I think question is asked for count of non-unique.
di_fsantos Muito bom ajudou muito o comentário.
shravil_potdar i applied the same.. but its showing me the following error
ERROR 1146 (42S02) at line 1: Table 'run_mzazybtiy9u.staton' doesn't exist
er_shreyansh1991 i think your spelling of station is wrong. you used staton in place of station
shravil_potdar it just missed from my eyes..
Vijaya_Wakode how to get count of : select city from station intersect select distinct city from station; ??
shravil_potdar select count(*) from station where city in ((select city from station) intersect (select distinct(city) from station));
sivarama This should work
select (select count(city) from station)-(select count(distinct(city)) from station);
h851212656 That's works in Mysql,fails in Oracle.
akbarmulla89 In Oracle DB : SELECT (COUNT(CITY) - COUNT(DISTINCT CITY)) FROM STATION;
niladri9211 Yes the same going here.It is not working in Oracle.Ypu may replace - with minus operator.
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;
Feel free to ask if you have any questions :)
adarshsingh0205 simply put this command SELECT (count(CITY)- COUNT(DISTINCT CITY)) FROM STATION;
lochek_alon HM... So it is always much easier than I think. I need to learn how to think towards the simplest solution. Thank you, man!
saijyothimalathi select count(city)-count(distinct city) from station;
siken_dongol question was not clear, i have to see discussion to understand the question.
pooja_cse2308 SELECT COUNT(CITY)- COUNT(DISTINCT CITY) FROM STATION;
epnasis EXACTLY THAT :)
satheshkumarcs it's not working in mysql
scaohuu How would you do this using variables?
Sort 270 Discussions, By:
Please Login in order to post a comment