Sql Server 2012 error 8120. group by error on a simple select query

Question

This is my table creation code

      CREATE TABLE "transactions" (
    "id" INT NOT NULL,
    "tno" INT NOT NULL,
    "pno" INT NOT NULL,
    "sno" INT NOT NULL,
    "accode" INT NOT NULL,
    "acname" VARCHAR(50) NOT NULL,
    "date" DATE NOT NULL,
    "truck" VARCHAR(50) NULL,
    "weight" DECIMAL NULL,
    "quality" INT NULL,
    "debit" MONEY NOT NULL,
    "credit" MONEY NOT NULL,
    "amount" MONEY NOT NULL,
    "comment" TEXT NULL,
    PRIMARY KEY ("id")
);

I want to get some data from this table by this query.

 Select * from  transactions GROUP BY tno

but it gives me error:

Msg 8120, Level 16, State 1, Line 1
Column 'transactions.tno' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

finaly i got solution . my answer is

   select yt.id, yt.tno, yt.accode, yt.acname, yt.pno, yt.comment, yt.amount, yt.date from transactions yt inner join( select tno, max(id) id from transactions ss group by tno ) ss on yt.id = ss.id and yt.tno = ss.tno order by yt.tno desc

Google search *"How to use group by"*. Please describe what you are after, no point using `groupby` if you going to `select *` — huMpty duMpty, May 13 '14 at 08:48
@huMpty duMpty i actuly want to get data of distinct value of tno with all the columns — user3631434, May 13 '14 at 08:55
i am new to sql so i need help. i dnt know why people not welcome begineers. — user3631434, May 13 '14 at 08:57
@user3631434 grouping would mean combining rows together to show totals or min/max values. so you can't have all of the rows and have them grouped. Are you looking to order by, rather than group, so that all tno's are together? — Tanner, May 13 '14 at 08:58
@user3631434 We do welcome beginners. But beginners want to learn, rather than just copy and paste code until it works. — Bob Tway, May 13 '14 at 08:58
@Tanner i need distinct with tno. and all columns with selected tno — user3631434, May 13 '14 at 09:14
@user3631434 if you want some help I suggest you add some dummy data to this SQL Fiddle I have set up for you: http://sqlfiddle.com/#!6/fb13c/1 and then update the question to show what you would like the output to look like based on the updated fiddle — Tanner, May 13 '14 at 09:14
@user3631434 if you do what I suggested, I will happily help you. if you continue to ask the same question without giving it proper context then I can't help you. — Tanner, May 13 '14 at 09:16
@Tanner check this http://sqlfiddle.com/#!6/2ca30/3 it just select one column tno. but i want the complete row of the selected tno. i hope now you can understand — user3631434, May 13 '14 at 09:22
sorry, that' not enough for me to answer, make a few rows with something near to real data and edit the question with what you would like to see as your output. perhaps reduce the number of columns so it's easier to look at. — Tanner, May 13 '14 at 09:24
@Tanner if i want select all like this query http://sqlfiddle.com/#!6/2ca30/4 then it start distinct every column — user3631434, May 13 '14 at 09:24
@Tanner this is what i was looking for. select yt.id, yt.tno, yt.accode, yt.acname, yt.pno, yt.comment, yt.amount, yt.date from transactions yt inner join( select tno, max(id) id from transactions ss group by tno ) ss on yt.id = ss.id and yt.tno = ss.tno order by yt.tno desc — user3631434, May 13 '14 at 12:30

score 1 · Answer 1 · answered May 13 '14 at 08:45

1

GROUP BY allows you to collect data about mathematical operations and collect them under non-mathematical ones.

For example

SELECT truck, SUM(tno)
FROM transactions
GROUP BY truck

Would give you the total values of TNO field for each type of "truck" you have in your table. The mathematical operator here is SUM.

So if your table contained this:

truck   tno
------------
dodge    44
viper    33
dodge    22

The above query would return

truck   tno
------------
dodge    66
viper    33

You're using GROUP BY without a math operator, so the query fails. To just get your data use

 Select * from  transactions

Or if you do want to use a math operator to get some data, tell us more clearly what you're trying to achieve rather than just posting table structure and a failing query.

answered May 13 '14 at 08:45

Bob Tway

9,301
17
80
162

i need all the columns in result. but with your query it show error on more then 2 columns. – user3631434 May 13 '14 at 08:51
You're still not telling us what you want. If you just want all your data, use the query at the bottom - Select * from transactions - but if you want an actual group by, tell us what math operator you're using on what column and what the groups you need are. Alternative, take the advice in the comments and actually *read about* and *understand* the language you're trying to use. My example is supposed to help demonstrate to you *how* basic sql queries work. Take the time to think about it, rather than rushing to copy code. – Bob Tway May 13 '14 at 08:56
i want all columns with distinct of tno – user3631434 May 13 '14 at 08:58
Select distinct(tno),pno,date,accode,acname,truck, weight, quality, amount from transactions i use this query but it distinct all the columns written above. i jst want to distinct tno – user3631434 May 13 '14 at 08:58
EDIT - oh, I see. Try SELECT DISTINCT tno FROM transactions – Bob Tway May 13 '14 at 09:00
it only return tno column – user3631434 May 13 '14 at 09:02
1

@MattThrower A+ for effort, but please give up and do something useful :-) – Tanner May 13 '14 at 09:03
Yes, it will, because you asked for "just distinct tno", which is what I gave you. Sorry, I give up. I suggest you do some more reading on basic SQL so you can at least ask your questions in a meaningful manner. Try http://www.w3schools.com/sql/default.asp – Bob Tway May 13 '14 at 09:03
@Tanner Yes. I'm pretty new to actually trying to answer questions rather than just ask them. Learning the hard way that it's rarely worthwhile :) – Bob Tway May 13 '14 at 09:04
@MattThrower it distict tno but how to get other columns with this query? i want all record of that selected tno row – user3631434 May 13 '14 at 09:05
@MattThrower you can help me. you are very near to result. i was just stuck on this topic from morning. by searching and searching. tired of searching and then post question here. please complete this. – user3631434 May 13 '14 at 09:10

Sql Server 2012 error 8120. group by error on a simple select query

1 Answers1