Solved

Column xxx is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Posted on 2011-03-02
8
774 Views
Last Modified: 2012-05-11
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!


0
Comment
Question by:chenyuhao88
  • 5
  • 2
8 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 300 total points
ID: 35023803
>> select MAX(mas_no), * from Job

Few recommended coding practices:

1. Don't have * in your SELECT statement and type in all columns.
2. Don't leave a column without a name. For aggregated/ derived columns you can use alias like

select MAX(mas_no) as Max_mas_no, *
from Job
GROUP BY job.* -- Replace * with all column names present in the table.

in order to avoid issues like this.

>> select count(r.Rec_Seq),Job.* from Job
Left JOIN [References] r on r.Rec_Seq  = Job.Seq
GROUP BY
   r.Rec_Seq,

If you want to avoid all columns in GROUP BY, then you can use like this

select count(r.Rec_Seq) over ( partition by job.primar_key_or_some_other_column) cnt ,Job.*
from Job
Left JOIN [References] r on r.Rec_Seq  = Job.Seq
0
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 200 total points
ID: 35023831
>>P.S. My final problem is i need to get a count(..) from another table, plus all Job table columns, like:

You could avoid using GROUP BY by getting the count in a subquery before joining the tables
select Job.*, r.ReqCount 
from Job Left JOIN
    (SELECT Req_Seq, COUNT(1) ReqCount
       FROM [References]
       GROUP BY Req_Seq
    ) r on r.Rec_Seq = Job.Seq

Open in new window

0
 

Author Comment

by:chenyuhao88
ID: 35023865
Hi rrjegan17, you solution looks quite good. just one issue: the result gives me duplicated Job records when that job has multiple records in References table.

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!


0
 

Author Comment

by:chenyuhao88
ID: 35023876
Thomasian, your one is the same as my solution! Thanks mate!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35023891
>> 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
0
 

Author Comment

by:chenyuhao88
ID: 35024008
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!!

0
 

Author Comment

by:chenyuhao88
ID: 35024061
Hi rrjegan17,

Don't worry about that. I changed column type to Varchar(Max) and it works perfect!

Thanks again all!!

0
 

Author Closing Comment

by:chenyuhao88
ID: 35024065
2 different solution, and 1st one is perfect!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now