Cannot use GROUP BY clause the way I want to?

I'm trying to use a GROUP BY clause in my SQL statement when retieving information from an SQL Server DB. The only problem is that it won't let me 'SELECT' columns from the database that are not part of the GROUP BY clause. Here is my example:

This works:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA, ColumnB

This does NOT work:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA

It simply will not let me have ColumnB in the SELECT clause unless I put it in the GROUP BY clause. Is there any way around this? Because I need both columns to display in the page, but I only want to group them by one column.

I'm coming from MySQL, and in MySQL what I want to do is perfectly legal. However, in SQL Server it's not...

Any ideas?Let me try to answer your question.

Create a simple table first:
ColumnA, ColumnB
x, y
x, z

If you group it by ColumnA, it returns a single row of 'x'. What value of ColumnB you want diplay? 'y' or 'z'?

Each group of ColumnA may have multiple values for ColumnB. Now you know why SQL won't allow you have columnB in SELECT clause.

What you can do is to use functions like MIN(), MAX()..to choose the a single value from inside the group. Or aggregation fuctions if columnB is numeric:

SELECT ColumnA, Min(ColumnB) FROM MyTable GROUP BY ColumnA

It shows the minimum ColumnB value inside the group.|||Group by is not really useful unless you want to get some aggregate value such as SUM, AVG, MIN, etc.

What you can do is:

select a.ColA, a.ColB, b.Total
from Table1 a
join
(select ColA, Sum(ColC) as Total
from Table1
group by ColA) b
 on a.ColA = b.ColA

This will give you what I think you want. You'll have all the values of ColA and ColB with the total based on ColA alone. Is DISTINCT what you're really after?|||I was having a similar problem, and DISTINCT worked for me:

"SELECT DISTINCT tblRequest.MgmntID, tblMgmnt.MgrName FROM tblRequest"
I had been trying GROUP BY, UNIQUE, but couldn't get it to work.
Thanks pdraigh
SMC