2

I have two table t1 and t2, Where t1 has the data like:

Id. Name
1. Ab
2. Dc
3. Cd

t2 has the data as given:

Id. Revenue
1. 100
2. 0
3. 200

And my SQL query is:

select t1.id ,t1.name,sum(t2.rev)
from t1
inner join t2 on t1.id= t2.id
where t1.id=100 and t2.Revenue <> 0
group by t1.id
Dale K
  • 25,246
  • 15
  • 42
  • 71
mahadev dhyani
  • 77
  • 9
  • 1
    Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Muhammad Saqlain Dec 05 '18 at 07:06

3 Answers3

1

Just put to GROUP BY columns that you want to select:

select t1.id ,t1.name,sum(t2.rev)
from t1
inner join t2 on t1.id= t2.id
where t1.id=100 and t2.Revenue <> 0
group by t1.id, t1.name, t2.rev

The error means that that you've got SUM of t2.rev field - one row, but SQL Server has also many rows with calculated column and it does not know what a row exactly should be chosen.

UPDATE:

If one of your some column has type such as text, ntext or image, then you should cast it to NVARCHAR type:

select t1.id ,t1.name,sum(t2.rev)
from t1
inner join t2 on t1.id= t2.id
where t1.id=100 and t2.Revenue <> 0
group by t1.id, CAST( t1.name AS NVARCHAR(100)), CAST( t2.rev AS  NVARCHAR(100))

UPDATE 1:

TEXT, NTEXT and IMAGE are old type of variable and there types are deprecated. So these types be replaced or casted by the corresponding types VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).

If you have just one column of type of TEXT, then just CAST just this column:

select t1.id ,t1.name,sum(t2.rev)
from t1
inner join t2 on t1.id= t2.id
where t1.id=100 and t2.Revenue <> 0
group by t1.id, CAST( t1.name AS NVARCHAR(100)), t2.rev
StepUp
  • 36,391
  • 15
  • 88
  • 148
  • After applying this query i am getting (the text ,ntext and image data types can not be compared or sorted,except when using is null or like operator) error – mahadev dhyani Dec 06 '18 at 04:03
  • Thanks @stepup it is working now but my column datatype was in decimal. – mahadev dhyani Dec 06 '18 at 16:50
  • @mahadevdhyani feel free to ask any question. Of it helps to you, you can mark it as an answer to simplify future search of other people. – StepUp Dec 06 '18 at 17:35
  • 1
    But i just want explaination that only name was in text apart from that all were in decimal then why was this error shown in sql server. – mahadev dhyani Dec 06 '18 at 17:46
0

If anything, the column causing the error would be t1.name1 Since the name is completely dependent on the ID, you can artificially add it to the group by clause without harming the query's correctness:

select t1.id ,t1.name,sum(t2.rev)
from t1
inner join t2 on t1.id= t2.id
where t1.id=100 and t2.Revenue <> 0
group by t1.id, t1.name
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • After applying this query i am getting (the text ,ntext and image data types can not be compared or sorted,except when using is null or like operator) error – mahadev dhyani Dec 05 '18 at 10:03
0

if you want to display any column and you used group by than you must group by one those column also

select t1.id ,t1.name,sum(t2.Revenue) from t1
inner join t2 on t1.id= t2.id where t1.id=1 and t2.Revenue <> 0 group by t1.id,t1.Name
Uday
  • 1
  • 1