Grouping with Datareport

[R031E5]

Hi! First of all I want to say hello to all the community, my name's Dennis and I will be glad to help you all in any subject. I've a problem trying to group data in a datareport, here's a clear example:

I've a SQL server with this table:

Employees

CompanyID Name Salary
1 Dennis $1000
2 Bob $1000
2 Martin $900
3 Elliot $1000

I want to create a Datareport that groups the employees by salary. E.g.

Salary report

*********** $1000 ***********
CompanyID Name
1 Dennis
2 Bob
3 Elliot

***********$900*************
CompanyID Name
2 Martin


I do have a little bit of experience with VB's datareport, but it's very basic, simple reporting.

Can someone help me with this report?:confused: Thanks! ;)

[KGComputers]

hi,

have you tried using the sql group by?


greg:)

[R031E5]

Yup, but it keeps telling me that I need to do something with my other 2 rows, because if not SQL cannot group them, if for example I have only 1 row that I want to group, i'd just do this:

SELECT CompanyId FROM Employees GROUP BY CompanyId

The problem is when I try to do it with 2 or more rows, example:

SELECT CompanyId, Name, Salary FROM Employees GROUP BY CompanyId

With that query, SQL complains about what should it do with the next column "Name". Error says:

Msg 8120, Level 16, State 1, Line 1
Column 'Employees.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please can somebody help me please? :cry: