Group By problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By problem

Author

Topic

lairpr74
Starting Member

8 Posts

Posted - 2009-01-05 : 21:39:36

I am trying to group some info on a database by type and is only returning one row. The database is in mysql.

SELECT boardMembers_tbl.bType, boardMembers_tbl.name, boardMembers_tbl.email
FROM boardMembers_tbl
GROUP BY boardMembers_tbl.bType

Any idea what is wrong?

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-05 : 22:20:19

Can you post the error you are getting?

Try giving all in the group by clause.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-05 : 22:49:05

Columns boardMembers_tbl.name, boardMembers_tbl.email
are invalid in the select list because they are not contained in either an aggregate function or the GROUP BY clause.

Syntactically the below is correct

SELECT boardMembers_tbl.bType, boardMembers_tbl.name, boardMembers_tbl.email
FROM boardMembers_tbl
GROUP BY boardMembers_tbl.bType,boardMembers_tbl.name, boardMembers_tbl.email

Jai Krishna

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-05 : 22:52:26

It might be syntactically correct, but it probably isn't what you want.

lairpr74, you need to apply an aggregate function such as MAX to the columns in the SELECT list that aren't part of the GROUP BY. Here is an example:

SELECT Column1, MAX(Column2) AS Column2
FROM Table1
GROUP BY Column1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-05 : 22:53:37

boardMembers_tbl.name, boardMembers_tbl.email are not contained aggregate function in the select list. or remove boardMembers_tbl.name, boardMembers_tbl.email in the group by clause

lairpr74
Starting Member

8 Posts

Posted - 2009-01-05 : 23:00:16

Thanks that gave me better results, however how can data data (types) be group by the same name and display the names bellow the type?
Type 1
member 1
member 2
member 3
Type 2
Member 4
Type 3
Member 5
Member 6

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-05 : 23:01:25

That is a presentation issue and should be handled by your application. Return the raw data from the database, and then format as necessary in the application code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lairpr74
Starting Member

8 Posts

Posted - 2009-01-05 : 23:09:07

Sorry to ask, but how can I format the data?
Any example?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 00:04:11

I'm not a developer, but I know you can easily do that type of thing in a reporting tool such as Reporting Services. What application language are you using? If it's a .NET app, there's probably someone here who can help you with the specifics as .NET is the most widely used platform when dealing with SQL Server as the backend.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 02:41:05