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

You only use `GROUP BY` when using an aggregate function like `SUM`, `COUNT`, `MIN`, `MAX`, etc. Do you mean `ORDER BY`? — Ken Keenan, Jul 20 '12 at 19:00
Can you provide more details on what you are trying to do? Possibly post some sample data. — Taryn, Jul 20 '12 at 19:02
I think the answer below are sound ideas, but I'm wondering if it's the "messes up the data" part that's really causing you confusion. I don't think we know what you mean by that until we know what you're trying to accomplish. I'm starting to agree with the `ORDER BY` note above. — shawnt00, Jul 20 '12 at 23:15

score 9 · Answer 1 · answered Jul 20 '12 at 19:07

9

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

answered Jul 20 '12 at 19:07

Bert

80,741
17
199
164

I was about to suggest the same approach. I guess other people didn't look at the sentence "actually grouping with the others messes up the data". Therefore joining appears to be better than an aggregate function. – Farhan Jul 20 '12 at 19:22
If you're using 2008, `CROSS APPLY` and `OUTER APPLY` might also be useful. – mikurski Jul 20 '12 at 21:35

score 4 · Answer 2 · answered Jul 20 '12 at 18:58

4

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

answered Jul 20 '12 at 18:58

Aducci

26,101
8
63
67

How can you be sure the MIN will be accurate to use? After reading the sentence "actually grouping with the others messes up the data", a join seems to be a better approach. – Farhan Jul 20 '12 at 19:23
This could mean that the fieldb, fieldc, etc come from differnt records and is NOT a good approach. – HLGEM Jul 20 '12 at 20:37

score 1 · Answer 3 · answered Jul 20 '12 at 20:27

1

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

answered Jul 20 '12 at 20:27

gloomy.penguin

5,833
6
33
59

score -1 · Answer 4 · edited May 18 '17 at 07:03

-1

select 
  o.Customer, count(*) as ItemCount
from 
  Orders o
inner join 
  OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer

edited May 18 '17 at 07:03

Pang

9,564
146
81
122

answered May 18 '17 at 06:44

alireza

1

How to avoid GROUP BY in SQL view

4 Answers4