Grouping with Datareport

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

Code:

---

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

Code:

---

*********** $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! ;)

hi,

have you tried using the sql group by?


greg:)

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:

Code:

---

SELECT CompanyId FROM Employees GROUP BY CompanyId

---

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

Code:

---

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:

Code:

---

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: