SQL Server – Aggravating Queries- Are you also one of them?

Hey Folks,

This blog post deals about the aspects of GROUP BY clause which we might encounter it, if we aren’t aware of this concept. This has also done some developers to simply avoid aggregate queries and make the reporting tool to do the work.

If till now, you wouldn’t encounter any problem while using with the GROUP BY clause in your query design, then it would be because every column would have participated in the aggregate purpose of the query.

This will clear to you, if you carefully see this example:

Let see the table that we are using in the query:

Now, if I want to group the “Class” according to the “Salary”, and want to see the “S_Id” column also, then we might write a query like this:

SELECT S_Id,Class,
SUM(Salary) AS Salary
FROM Students
GROUP BY Class;

Then we will get an error:

Msg 8120, Level 16, State 1, Line 1

 Column ‘Students.S_Id’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Therefore, so whenever you use aggregate with SELECT statement, give all the Columns (present in SELECT statement) in GROUP BY except the aggregated column. So, if I want to see the “S_Id” of each student with “Class” and “Salary”, the query will be:

SELECT S_Id,Class,
SUM(Salary) AS Salary
FROM Students
GROUP BY S_Id,Class;

The result for this will be:

Hence, this was all about aggravating queries when we are dealing with GROUP BY for this blog post. And I know that aggravating queries is itself a very large topic, but here I have deal with one single aspect. But will be posting more from it, in the future.

Hope you like it 🙂

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook