Advertisements
Home > SQL Server Questions, SQL Tips > Include other columns while doing aggregates and GROUP BY – MSDN TSQL forum

Include other columns while doing aggregates and GROUP BY – MSDN TSQL forum


 
–> Question:

I need maximum mark scored each students and min mark scored each students.

I have done this way…

select ID, max(mark), min(mark) 
from table1 
group by ID

But i need more field as Class…. I don’t want include class field in group by clause
 

–> My Answer:

Try joining the query with the table, like:

select T1.*, T2.ID, T2.max_mark, T2.min_mark
from table1 T1
join (select ID,max(mark) as max_mark,min(mark) as min_mark
	from table1 
	group by ID
) AS T2
on T1.ID=T2.ID

 

–> shaahs Answer:

In this situation you can use the window functions:

select ID, Class, 
	max(mark) over(partition by ID), 
	min(mark) over(Partition by ID) 
from table1

… in this cause you don’t want to include the fields in group by clause.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: