How do I make my RANK () OVER query work in select?

Question

I have this table that I need to sort in the following way:

need to rank Departments by Salary;
need to show if Salary = NULL - 'No data to be shown' message
need to add total salary paid to the department
need to count people in the department

    SELECT RANK() OVER (
                ORDER BY Salary DESC
                )
            ,CASE 
                WHEN Salary IS NULL
                    THEN 'NO DATA TO BE SHOWN'
                ELSE Salary
                    ,Count(Fname)
                    ,Total(Salary) FROM dbo.Employees

I get an error saying:

Column 'dbo.Employees.Salary' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why so?

The `count(Fname)` is an aggregation function. In order to display another field (like `Salary`) next to it, it requires a `group by` clause on that other field. — Sander, Nov 14 '20 at 18:08
Your above query seems to lack an END for the case statement... Simple rule is that as soon as a query selects an aggregate (like count, sum, avg) all other columns (or expressions) need to be listed in the GROUP BY field list. In your statement adding `GROUP BY CASE WHEN Salary IS NULL THEN 'NO DATA TO BE SHOWN' ELSE Salary END ` would do the trick. — ub_coding, Nov 16 '20 at 09:48

Shad · Answer 1 · 2020-11-14T19:42:20.490

Column 'dbo.Employees.Salary' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why so?

The aggregate functions are returning a single value for the whole table, you can't SELECT a field alongside them it doesn't makes sense. Like say, you have a students table you apply Sum(marks) for the whole students table, and you are then also selecting student's name Select studentname in your query. Which student's name will the database engine select? Confusing

Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

I tried this- using inner query

SELECT RANK() OVER (ORDER BY SAL DESC) RANK,FNAME,DEPARTMENT
CASE 
    WHEN SAL IS NULL THEN 'NO DATA TO BE SHOWN'
    ELSE SAL
END
FROM
(SELECT COUNT(FNAME) FNAME, SUM(SALARY) SAL, DEPARTMENT
 FROM TESTEMPLOYEE
 GROUP BY DEPARTMENT) t

How do I make my RANK () OVER query work in select?

1 Answers1