srinath_deepika MySQL solution-
select (salary * months)as earnings ,count(*) from employee group by 1 order by earnings desc limit 1;
navdeep_singhAsked to answer Great :)
Mehtasachin PLEASE EXPLAIN
FreeFly19 ... GROUP BY 1 ...
means - group by first column from SELECT . The same pattern could be used for ORDER BY :
... ORDER BY 1 ...
order by earnings
wushengzhong What if they have multiple people tie with maximum earnings?
shankarpentyala The count function keeps count of the people having the same earnings ,since we are grouping by earnings.
zluxyoe Can group by be used with column alias "earning"? I'm confused, thank you so much.
rassel_dasgupta Yes, you can use group by with the column alias 'earning' or by 1 in this case since 1 can be used to refer to the first column in the SELECT statement(earnings).
sidanaraghav2591 I don't think group by 1 works for oracle,can someone please verify??
Muthukumar_T select max(months*salary),count(months*salary) from Employee where (months*salary) = (select max(months*salary) from Employee);
without group by...........
sephi1Asked to answer well done
yugoliang what does the group by 1 do?
prakruti7 'SELECT SALESMAN_NAME, SUM(SALES) FROM SALES GROUP BY 1' it will group by SALESMAN_NAME.
benkiviti grouping by the first expression you have at the select option so if you do select a,b,c grouping by 2,1 will give you groups in the order of b,a and the c expressions according to these groups
raghavb50 Thanks benkiviti for explaining new concept lucidly.
abcoder4 I don't think
GROUP BY <alias>works for Oracle. Can someone verify?jessevsilverman I can confirm that GROUP BY clearly seems to work for Oracle, altho remember that the table ALIAS works differently there as they forbid the 'AS' keyword for table names (it is ok with col names):
SELECT MAX_EARNINGS, COUNT(EMPLOYEE_ID) FROM (SELECT MAX(EARNINGS) AS MAX_EARNINGS FROM (SELECT MONTHS * SALARY AS EARNINGS FROM EMPLOYEE) EARNINGS_TABLE) MAX_EARNINGS_TABLE, EMPLOYEE WHERE MONTHS * SALARY = MAX_EARNINGS GROUP BY MAX_EARNINGS ;
Without indentation (HR wants us to enter these things as one long line!) I consider these completely write-only, who can make sense of that without indentation?? But, it works.jadhavpooja0695 we can reduce number of sub queries here. my version for above query : select (salarymonths)earning,count() from employee where (salarymonths)=(select max(salarymonths) earning from employee) group by (salarymonths);
bishaltamang11 1 here is the column number, so if you are taking select name,id from employee, name is the 1st column and id is 2nd. so if you want to group it by name so you can write group by 1, or if you want to group it by id then group by 2.
veraalena5555 SELECT MAX(months * salary) ||' '|| COUNT(*) FROM employee GROUP BY salary HAVING MAX(months * salary) = (SELECT MAX(months * salary) from employee);
batur Why you limited selection just with 1 person? What will be the answer if two or three people will have the same equal total salary and it will be equal?
crazy_vikas23 i think limit is applied after all group by and order by clauses
latteo This solution works 2 times slowly than subquery
WHERE salary * months = (select max(salary * months) FROM `Employee`)
ashukpratap here why ?
prome_nabid Because for each row selected, it will execute the where clause (so thus the select statement in subquery)
ndvo2710 For more literal code:
select salary*months as earnings, count(*) from employee group by earnings order by earnings desc limit 1;
For more lazy:
select salary*months as earnings, count(*) from employee group by 1 order by 1 desc limit 1
To use WHERE:
select salary*months, count(*) from employee where salary*months = (select max(salary*months) from employee)
Ankur_Beginning Will you please explain me?
zerofactiongames The order by statement is unecessary as you can sort the grouped items using asc or desc. You can simplify slightly by using
SELECT MONTHS * SALARY AS EARNINGS, COUNT(*) FROM EMPLOYEE GROUP BY EARNINGS DESC LIMIT 1;
MAbdElRaouf Thank you for sharing this.
bhavesh_gehani21 Can you please explain count(*) and not count(earnings)?
Thanks!
melanie_poirier1 the * means count the num of records, we dont want to count the earnings
gauravHD We can also use 1 in place of "AS EARNINGS". It makes the query length even smaller.
' ' ' SELECT SALARY*MONTHS, COUNT(*)
FROM Employee
GROUP BY 1 DESC
LIMIT 1; ' ' '
kumarvishwajeet1 The execution of any sql query starts with from clause then group by then so on... But, here in group by you have used alias name, I didn't understand how it worked , How groupby is aware about alias name.
I have verified and it is giving correct output
sanjeev1696 why count() will give u the value as 1,... what count() represents?
zedelta Considering perfromance, "To use WHERE:" would be more resource consuming than the other two. Nevertheless it is a solution :). My favorite is "For more lazy:"
pretty_evils great fast query
psaez nice =D
cbodhisatya the last solution with the where clause does not work
saur2702 rownum = 1 top limit any will work in this case
FYI :)
himanshumittal12 group by salary*months should also be there.
divyabansal30 [deleted]gcunha1996 Thank you, but you should also add a Group By clause at the end of the query using Where, like you did on the other queries!
dboggs95 To use a nested query in the where clause in MySQL, a group by clause is required. To do so, add 'as earnings' after 'salary * months', and then 'group by earnings' at the bottom.
sharmashruti100 its not working in oracle!
soumyaa_itian I am getting wrong answer for this.
mgomezmunoz92 [deleted]
amitasaurus [deleted]anadig This wont work in 'Oracle' because group by is executed before select in SQL . Am I right?
anukarsh_007 Amazing :)
jayraj18011997 this is not working for oracle
a_anand_IT110 Its a nice solution but can you explain this particular part
GROUP BY 1
zerofactiongames Adityaanand,
When referring to the code
select salary*months as earnings, count(*) from employee group by 1
"group by 1" is simply stating that you want to group by the data in Column 1.
Since the columns are created in the order they are declared, column 1 or just "1" in this case, is "earnings" since it is declared first.
Therefore in this situation: "Group by 1" is just shorthand for "Group by Earnings".
Cheers.
onkar_rana27 SELECT MAX(MONTHS*SALARY), COUNT(EMPLOYEE_ID) FROM EMPLOYEE where MONTHS*SALARY=(select max( MONTHS*SALARY) from EMPLOYEE);
sruthimallarapu Instead of SELECT MAX(MONTHS*SALARY) can we use SELECT MONTHS*SALARY? If not no can you explain me y?
nimish_gupta you don't use max then how your query able to output the max of the months*salary?
skitram Thats why there is order by months x salary desc 1 segment that shows the only one row with max value
nimish_gupta Then you should see this in mysql!! :)
select max(months*salary), count(*) from employee where salary*months = (select max(months*salary) from employee);
skitram That's right too..every problem has number of solutions it just depends on how you approach it. :-) happy coding!!!
nimish_gupta basically I write in this way coz I'm not good at group by and order by.. Can u plz help me on these two.!!
divya_gracie MS SQL
select top 1 salary*months, count(name) from employee group by salary*months order by 1 desc;
draghusqlbi [deleted]johnhillescobar Great query!
phdapplymike Very beautiful
mansisaxena_0402 This is not working in MySQL
nsirimailbox In sql server its Top 1 in mysql its limit 1
jainsunny0507 can u please explain me why we group only salary not salary*months
Herron Beautiful, my answer was very straight forward but not smart:
select max(salary*months), count(employee_id) from employee where salary*months = (select max(salary*months) from employee) group by salary*months
mayank_aggarwal3 why have you used group by 1?
johnhillescobar Because it is calling to only column created in the SELECT statement. You can also use salarymonths instead of 1.
pranjal_G will not work if there is more than 1 employee with max salary
Jiaying This answer is NOT reliable. "limit 1" will only show one MAX record - what if we have two or more records with the MAX earnings? Solution would be: select (salary * months) as earnings, count(*) from employee group by salary * months having earnings = ( select max(salary * months) from employee);
Ajay_EEE19 why shouldn't i use alias instead of 1
tadeuaugusto Hello, There..
This is my first post on discussions.. Here is a solution for ORACLE:
SELECT * FROM (SELECT MAX(MONTHS*SALARY), COUNT(EMPLOYEE_ID) FROM EMPLOYEE GROUP BY MONTHS*SALARY ORDER BY MONTHS*SALARY DESC) WHERE ROWNUM = 1;
farhadvaghari THIS IS AMAZING. THANK YOU.
raghusrinivas1 select max(salary*months),count(max(salary*months)) from employee group by (salary*months); can you please explain whats wrong with this query??
abhilash_psp you are printing multiple values. you haven't put any limits.
ajay16kumar71 [deleted]drerosdaniel Here is my somewhat similar Oracle solution:
select ms, (select count(employee_id) from employee where salary* months = ms) from (select max(salary* months) ms from employee);
sugandh_kverma could you please explain the code... thank you
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT * FROM ( SELECT Months * Salary, COUNT(*) FROM Employee GROUP BY (Months * Salary) ORDER BY Months * Salary DESC ) WHERE ROWNUM = 1;
Feel free to ask if you have any questions :)
deepakchinnu17 select salary*months as earnings,count(*) from city --where earnings=(select max(earnings) from city); (or)
select salary*months ,count(*) from city where salary*months=(select max(salary*months) from city); why this too aren't working in oracle and how yours is working; @marinskiysean1813 you need to add GROUP BY. COUNT is aggregation function:
SELECT months*salary AS total, COUNT(employee_id) AS cnt FROM Employee WHERE months*salary = (SELECT MAX(months*salary) FROM Employee) ;
harshitguptaary1 MS SQL Solution
select max(months*salary),count(months*salary) from employee where employee_id in( select employee_id from employee where months*salary in (select max(months*salary) from employee));
RodneyShag MySQL solution
From my HackerRank solutions.
SELECT salary * months AS earnings, COUNT(*) FROM Employee GROUP BY earnings ORDER BY earnings DESC LIMIT 1;
Let me know if you have any questions.
ashu_yad98 how can we use 'salary*months as earnings' as far as i konw select statement will be executed after group by statement.
RodneyShag I think GROUP BY happens after the SELECT statement.
haardiksharma For ORACLE junkies : select salary*months,count(*) from employee where salary*months = (select max(salary*months) from employee) group by salary*months;
This will work faster then using the Having Condition.
Initially I tried using - select salary*months,count(*) from employee group by salary*months having salary*months = (select max(salary*months) from employee);
But in this case the query cost was damn too high because of obvious reasons.
IwonaS88 I wanted to use rownum (Oracle) and came with sth like:
select * from (select earn, count(name) from (select months*salary earn, name from employee) group by earn order by earn desc) where rownum = 1;
Could it be simplified somehow? What do you think?
rahulkanodiya You can do it without rownum. select max(earnings), max(count(earnings)) from (select salary*months earnings, employee_id from employee) group by earnings;
eagle_ray is it efficient query?
select max(salary*months), count(employee_id) from employee where salary*months in (select max(salary*months) from employee);
lalitgurgaon2381 [deleted]
spdas MSSQL with total_sal as (select max(salary * months) as total_sal, count(*) as [no of employees] from employee group by salary * months) select top (1) total_sal, [no of employees] from total_sal order by total_sal desc
Zian007 Select MAX(salary*months), count(*)from Employee group by salary*months having salary*months=(Select MAX(salary*months)from Employee );
nivethaceg I think there is no need of first max.
Select salary*months, count(*)from Employee group by salary*months having salary*months=(Select MAX(salary*months)from Employee );
This one worked for me.
Sort 538 Discussions, By:
Please Login in order to post a comment