PLEASE EXPLAIN

'SELECT SALESMAN_NAME, SUM(SALES) FROM SALES GROUP BY 1' it will group by SALESMAN_NAME.

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

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.

i think limit is applied after all group by and order by clauses

here why ?

Will you please explain me?

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;

why count() will give u the value as 1,... what count() represents?

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:"

great fast query

nice =D

the last solution with the where clause does not work

rownum = 1 top limit any will work in this case

FYI :)

group by salary*months should also be there.

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!

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.

its not working in oracle!

select max(salary*months),count((select max(salary*months) from employee)) from employee;

help how the above code is wrong.

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.

select earn,howmany from (select (salary*months) as earn , count(salary*months) as howmany from employee group by salary*months order by earn desc) where rownum=1;

Thats why there is order by months x salary desc 1 segment that shows the only one row with max value

Great query!

Because it is calling to only column created in the SELECT statement. You can also use salarymonths instead of 1.

select max(salary*months),count(max(salary*months)) from employee group by (salary*months); can you please explain whats wrong with this query??

To get the respective counts of the total salaries. You could replace the '*' with 'months*salary'

I think GROUP BY happens after the SELECT statement.

HackerRank solutions.

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) ;

In his subquery it sorts total earnings by descending order, thus the 1st row is the top earner.

You can do it without rownum. select max(earnings), max(count(earnings)) from (select salary*months earnings, employee_id from employee) group by earnings;