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.
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.
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!
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; ' ' '
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.
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]
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
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);
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.
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 :)
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;
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.
sachin2213sharma for mssql select concat(max(months*salary),' ',count(*)) from employee where months*salary=(select max(months*salary) from employee)
steffiesjy You do not need to CONCAT and can omit the ,'' before COUNT.
The commas separating the months * salary and count (name or * , either works) is sufficient.
SELECT MAX(months*salary), COUNT(name) FROM employee WHERE (months*salary) = (SELECT MAX(months*salary) FROM employee)
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);
Sort 366 Discussions, By:
Please Login in order to post a comment