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:

1_SQL_Server_Aggravating_Queries_Are_you_also_one_of_them

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:

2_SQL_Server_Aggravating_Queries_Are_you_also_one_of_them

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published. Required fields are marked *