How to avoid GROUP BY in SQL view

Question

I have a sql view. Example is below. The problem is that I want to use all of the fields but I do not want to group by every field. How can I circumvent that? I only need to group on fieldA, but not the others...actually grouping with the others messes up the data I want to see. I'm using SQL Server 2008. Thanks!

select 
fieldA,
fieldB,
fieldC,
fieldD,
....
from my_table as a join other_table b on a.id=b.id
group by fieldA, fieldB, fieldC, fieldD

Solution 1

Generally when I want to select several fields, and aggregate something based on just one of them, I'll perform the aggregate in a derived table and join to the table I want to select from like so:

select fieldA, thingYouWantToAggregate, fieldB, fieldC, fieldD
from my_table
inner join
(
    select fieldA, thingYouWantToAggregate
    from my_table
    group by fieldA

) rsAggregated on rsAggregated.fieldA = my_table.fieldA

Solution 2

You need to use an aggregate function on the columns that you don't want to include in your group by. I usually use min

select 
  fieldA,
  min(fieldB),
  min(fieldC),
  min(fieldD),
....
from my_table as a join other_table b on a.id=b.id
group by fieldA

Solution 3

i really don't understand what you're looking for but i might as well throw it out there....

select distinct fieldA, ....
from table_name