How to use group by clause with one column while selecting all columns from table

Question

0.00/5 (No votes)

See more:

I have a table having three columns named OrderId,Seller,Date. I want to fetch data from table using group by seller but it works only when i write query as

SQL

select seller from tablename where OrderId=6 Group By Seller

but it gives error when i write query as follows

SQL

select *from tablename when<big></big> OrderId=6 Group By Seller

Error: tablename.OrderId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

How to resolve it. Is there any other way to fetch data without repeatation of same row

Posted 5-Jan-14 20:23pm

Member 7790559

Updated 5-Jan-14 20:42pm

Kornfeld Eliyahu Peter

v2

Add a Solution

Comments

Amir Mahfoozi 6-Jan-14 2:52am

It seems that it is a UI problem and not a SQL one. Just get plain data ordered by seller and group them in UI (whether report or grid).

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2014-01-05T20:44:00

Solution 1

When you want to group rows together you have to tell SQL how to group them.
Using * (bad habit anyway) tells SQL to bring all the fields but tells nothing about how to group them over different rows...
http://technet.microsoft.com/en-us/library/ms177673(v=sql.100).aspx[^]

Posted 5-Jan-14 20:44pm

Kornfeld Eliyahu Peter

Rajesh_DotNet · Answer 2 · 2014-01-05T20:49:00

Solution 2

Hi..,

On using GroupBy, you must mention the column names that exists in the select query.

Include OrderId also in GroupBy caluse.

Posted 5-Jan-14 20:49pm

Rajesh_DotNet

Member 14935747 · Answer 3 · 2020-09-10T23:50:00

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
8 Muffy 24 Indore 10000.00

SELECT * FROM CUSTOMERS
where AGE in
(SELECT AGE
FROM CUSTOMERS
GROUP BY AGE
HAVING COUNT(AGE) >= 2)
order by ID