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? 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?