Group by on single column not on all column

Question

0.00/5 (No votes)

See more:

Quote:
I have a table UserType as follows:

Quote:

HTML

ID	UserType
1	Admin
2	Marketing Manager
3	Hotel Manager
4	Playlist Programmer
5	Support Manager

Quote:
And another table User as follows:

HTML

ID  Name UserType LocationID
1   TT      1         1
2   AA	    2	      1
3   BB      1         2
4   CC      2         3

Quote:
I want to group by on single column UserType and not by all the column in the select list.
When I use the following query its giving error.How to do that?

SQL

Select		usr.ID,
			usr.FirstName,
			usr.LastName,
			usr.Email,
			usr.ContactNo,
			lctn.LocName ,
			IsActive,
			CreatedBy,
			utyp.UserType
			
	 from	[User] usr,
		    Location lctn,UserType utyp
     where usr.Location_id=lctn.LocID	
     and  usr.UserType_ID=utyp.ID	
     --group by  utyp.UserType

Posted 29-May-13 4:32am

Tirthankar Dutta

Add a Solution

Comments

[no name] 29-May-13 10:40am

"the following query its giving error", sorry I have used up all of my mind reading credits for the week, perhaps you can share which error, out the of the many errors that it could be, the one that you are getting.

CHill60 29-May-13 10:49am

The simple answer is that you can't http://msdn.microsoft.com/en-us/library/ms177673.aspx[^]. Why are you trying to group when you are not retrieving anything such as count or sum. Use the Improve question link to tell us what you are actually trying to achieve

David_Wimbley 29-May-13 10:54am

You can do a group by without having to do count or sum. Group By comes in hand with a join that returns multiple similar rows.

CHill60 29-May-13 11:01am

True ... but we still need the OP to explain what he is actually trying to do

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2013-05-29T04:53:00

Based on your question/sample query you are wanting to group by the single Column userType.

If you wish to group by one column your select query can only be for one column (or an aggregate function like COUNT or SUM).

So this

SQL

Select		usr.ID,
			usr.FirstName,
			usr.LastName,
			usr.Email,
			usr.ContactNo,
			lctn.LocName ,
			IsActive,
			CreatedBy,
			utyp.UserType
			
	 from	[User] usr,
		    Location lctn,UserType utyp
     where usr.Location_id=lctn.LocID	
     and  usr.UserType_ID=utyp.ID	
     --group by  utyp.UserType

Would need to be changed to this

SQL

Select utyp.UserType
from [User] usr,
     Location lctn,UserType utyp
     where usr.Location_id=lctn.LocID	
     and  usr.UserType_ID=utyp.ID	
     group by  utyp.UserType

This will allow you to group by the single column based on how i understand your question. For every column you wish to group by you must add it to both your group by and your select statement.

So if you have 2 columns in your group by, those 2 columns should be in your select statement.