Query Help
-
I have the following query that works, however; I need add another field "client_id" that I need to tie to client name in another table. I can't even add the client_id from the job_tickets table with this error: "select coMsg 8120, Level 16, State 1, Line 1 Column 'job_ticket.CLIENT_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I need to display the "client_name" feild from "Clients" table that based off of dbo.job_tickets.client_id...I can do another inner join in the below starement? How do I achieve this? This works....I just to add the aforementioned select count(job_ticket_id) as 'Total', problem_type.problem_type_name 'Problem Type' from job_ticket inner join problem_type on dbo.problem_type.problem_type_id = job_ticket.problem_type_id where report_date between '1/01/2007' and '1/31/2007' group by problem_type.PROBLEM_TYPE_NAME any help is greatly appreciated.
Regards, Hulicat
-
I have the following query that works, however; I need add another field "client_id" that I need to tie to client name in another table. I can't even add the client_id from the job_tickets table with this error: "select coMsg 8120, Level 16, State 1, Line 1 Column 'job_ticket.CLIENT_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I need to display the "client_name" feild from "Clients" table that based off of dbo.job_tickets.client_id...I can do another inner join in the below starement? How do I achieve this? This works....I just to add the aforementioned select count(job_ticket_id) as 'Total', problem_type.problem_type_name 'Problem Type' from job_ticket inner join problem_type on dbo.problem_type.problem_type_id = job_ticket.problem_type_id where report_date between '1/01/2007' and '1/31/2007' group by problem_type.PROBLEM_TYPE_NAME any help is greatly appreciated.
Regards, Hulicat
Hulicat wrote:
Column 'job_ticket.CLIENT_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Then add the column to the GROUP BY clause, just like the error message suggested you do.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Hulicat wrote:
Column 'job_ticket.CLIENT_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Then add the column to the GROUP BY clause, just like the error message suggested you do.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
Right I know that would seem obvious, however; if I group by client_id. Then I get the same error for "problem_type.PROBLEM_TYPE_NAME" I did not know you could group by more than one column; I have not seen that I threw it in and it worked. ~
Thanks~ -- modified at 20:14 Friday 11th May, 2007Regards, Hulicat