Can you give me Alternative to Group By sql server 2008

Question

Hi,

I have a situation where I have a Select with 6 fields but I only want to group by 3 fields. As you know this looks not possible. What do you do in these situations?

Thanks for any feedback

Answer 1

But what do you want to do with the 3 fields that are not grouped? You need to apply an aggregate function on each of them to make it a correct statement.

Answer 2

It looks like you have a misunderstanding of how group by works. Look at this example:

SELECT
    SalesOrderID,
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID

All included columns must be either aggregated (as with LineTotal above - SUM) or grouped (as with SalesOrderID above). This will give a separate LineTotal sum for each SalesOrderID.

Group By doesn't make sense without any aggregates.

Answer 3

Something like this:

SELECT
  Col1, Col2, Col3
, MAX(Col4) AS Col4
, MAX(Col5) AS Col5
, MAX(Col6) AS Col6
FROM Table1
GROUP BY 
  Col1, Col2, Col3

Or this, if you want the values for the non-grouped columns to come from the same row:

SELECT * FROM (
  SELECT *, _SeqNo = ROW_NUMBER() OVER (
    PARTITION BY Col1, Col2, Col3 
    ORDER BY (SELECT 1))
  FROM Table1) a
WHERE _SeqNo = 1