Grouping by single column but returning all the columns without including other columns in aggregate function

Question

I am working on an SQL query which should group by a column bidBroker and return all the columns in the table.

I tried it using the following query

select  Product,
    Term,
    BidBroker,
    BidVolume,
    BidCP,
    Bid,
    Offer,
    OfferCP,
    OfferVolume,
    OfferBroker,
    ProductID,
    TermID 
from canadiancrudes 
group by BidBroker

The above query threw me an error as follows

Column 'canadiancrudes.Product' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there any other way which returns all the data grouping by bidBroker without changing the order of data coming from CanadadianCrudes?

What are you trying to accomplish with this grouping? Some kind of example data + expected output would help to understand a lot more. — James Z, Apr 08 '15 at 15:16
possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:37

score 1 · Answer 1 · answered Apr 08 '15 at 15:20

First if you are going to agregate, you should learn about agregate functions. Then grouping becomes much more obvious.

I think you should explain what you are trying to accomplish here, because I suspect that you are trying to SORT bu Bidbroker, rather than grouping.

score 1 · Answer 2 · answered Apr 08 '15 at 15:27

If you mean you want to sort by BidBroker, you can use:

SELECT  Product,Term,BidBroker,BidVolume,BidCP,Bid,Offer,OfferCP,OfferVolume,OfferBroker,ProductID,TermID
FROM canadiancrudes
ORDER BY BidBroker

If you want to GROUP BY, and give example-data you can use:

SELECT c1.Product,c1.Term,c1.BidBroker,c1.BidVolume,c1.BidCP,c1.Bid,c1.Offer,c1.OfferCP,c1.OfferVolume,c1.OfferBroker,c1.ProductID,c1.TermID
FROM canadiancrudes c1
WHERE c1.YOURPRIMARYKEY IN (
    select MIN(c2.YOURPRIMARYKEY) from canadiancrudes c2 group by c2.BidBroker
)

Replace YOURPRIMARYKEY with your column with your row-unique id.

score 0 · Answer 3 · answered Apr 08 '15 at 16:28

0

As others have said, don't use "group by" if you don't want to aggregate something. If you do want to aggregate by one column but include others as well, consider researching "partition."

answered Apr 08 '15 at 16:28

APH

4,109
1
25
36

Grouping by single column but returning all the columns without including other columns in aggregate function

3 Answers3