chenyuhao88
asked on
Column xxx is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
Dear All,
I have a very simple query which executed no problems:
select * from Job
However if I just want to add 1 more stuff, it complains the error in the title:
select MAX(mas_no), * from Job
I know I could use query like:
select top 1 * from Job order by mas_no desc
to replace. But my question is: if I still want to use my query, how to do that? I don't want to list all columns in Job table like:
select MAX(mas_no), * from Job
group by Job.seq, job.type, job.xxx, job.yyy......
because there are too many colums.....
P.S. My final problem is i need to get a count(..) from another table, plus all Job table columns, like:
select count(r.Rec_Seq),Job.* from Job
Left JOIN [References] r on r.Rec_Seq = Job.Seq
GROUP BY
r.Rec_Seq,
(... do not want to list all job's columns...)
Thanks heaps!
I have a very simple query which executed no problems:
select * from Job
However if I just want to add 1 more stuff, it complains the error in the title:
select MAX(mas_no), * from Job
I know I could use query like:
select top 1 * from Job order by mas_no desc
to replace. But my question is: if I still want to use my query, how to do that? I don't want to list all columns in Job table like:
select MAX(mas_no), * from Job
group by Job.seq, job.type, job.xxx, job.yyy......
because there are too many colums.....
P.S. My final problem is i need to get a count(..) from another table, plus all Job table columns, like:
select count(r.Rec_Seq),Job.* from Job
Left JOIN [References] r on r.Rec_Seq = Job.Seq
GROUP BY
r.Rec_Seq,
(... do not want to list all job's columns...)
Thanks heaps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thomasian, your one is the same as my solution! Thanks mate!
>> just one issue: the result gives me duplicated Job records when that job has multiple records in References table.
Then try this..
select DISTINCT count(r.Rec_Seq) over ( partition by job.Seq) cnt ,Job.*
from Job
Left JOIN [References] r on r.Rec_Seq = Job.Seq
Then try this..
select DISTINCT count(r.Rec_Seq) over ( partition by job.Seq) cnt ,Job.*
from Job
Left JOIN [References] r on r.Rec_Seq = Job.Seq
ASKER
Hi rrjegan17,
Here is the error, which I think it's because there are 5 columns in Job table are "Text" type.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
how could I do that? (assume not change from Text to varchar(max))
Thanks again!!
Here is the error, which I think it's because there are 5 columns in Job table are "Text" type.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
how could I do that? (assume not change from Text to varchar(max))
Thanks again!!
ASKER
Hi rrjegan17,
Don't worry about that. I changed column type to Varchar(Max) and it works perfect!
Thanks again all!!
Don't worry about that. I changed column type to Varchar(Max) and it works perfect!
Thanks again all!!
ASKER
2 different solution, and 1st one is perfect!
ASKER
I found another solution like:
select match.cnt, j.* from Job j
left join
(
select Rec_Seq,COUNT(*) as cnt
from [References]
group by Rec_Seq
) match
on match.Rec_Seq = j.Seq
If my Job table has 200 rows of records, and it gives me exact 200 rows of record, plus 1st column tells me how many matching records in References table.
Is it possible to further change your solution?
Thanks again!