CODEWITHSUNDEEP
sqlsql-server
King
King

Reputation: 53

Group by week query getting error

My SQL group by week query is like below but in that am getting following error.

Column 'timesheet.timesheet_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can anybody help me in this regard

My query :

select 
   DatePart(week, timesheet_date),
   count(timesheet.timesheet_id) as TimeSheetID,
   timesheet.timesheet_date,
   timesheet.start_time,
   timesheet.end_time, 
   timesheet.timesheet_status_id,
   timesheet.is_deleted,
   timesheet.created_by,
   timesheet.modified_by,
   timesheet.created_date,
   task.name,
   project.name,
   [user].first_name, [user].last_name 
from 
   timesheet, task, project, [user] 
where 
   task_id = (select task_id from project_task 
              where project_task_id = timesheet.project_task_id) 
   and project_id = (select project_id from project_task 
                     where project_task_id = timesheet.project_task_id) 
   and [user].user_id = timesheet.user_id 
   and timesheet.user_id = 30 
group by 
   timesheet.timesheet_id

Upvotes: 0

Views: 91

Answers (2)

Neels
Neels

Reputation: 2543

Try this:

select  DatePart(week, timesheet_date),
        count(timesheet.timesheet_id) as TimeSheetID,
        timesheet.timesheet_date,
        timesheet.start_time,
        timesheet.end_time,
        timesheet.timesheet_status_id,
        timesheet.is_deleted,
        timesheet.created_by,
        timesheet.modified_by,
        timesheet.created_date,
        task.name,
        project.name,
        [user].first_name,
        [user].last_name 
from    timesheet,
        task,
        project,
        [user] 
where   task_id = (select task_id from project_task where project_task_id = timesheet.project_task_id) 
and project_id = (select project_id from project_task where project_task_id = timesheet.project_task_id) 
and [user].user_id = timesheet.user_id 
and timesheet.user_id =30 
group by timesheet.timesheet_id,
        timesheet.timesheet_date;

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166486

From the exception message, I will assume SQL Server.

for aggregations (SUM/MAX/MIN/COUNT) you need tol include all columns in the GROUP BY that is not part of an aggregate.

So for your query, you would have something like

select  DatePart(week, timesheet_date),
        count(timesheet.timesheet_id) as TimeSheetID,
        timesheet.timesheet_date,
        timesheet.start_time,
        timesheet.end_time,
        timesheet.timesheet_status_id,
        timesheet.is_deleted,
        timesheet.created_by,
        timesheet.modified_by,
        timesheet.created_date,
        task.name,
        project.name,
        [user].first_name,
        [user].last_name 
from    timesheet,
        task,
        project,
        [user] 
where   task_id = (select task_id from project_task where project_task_id=timesheet.project_task_id) 
and project_id = (select project_id from project_task where project_task_id=timesheet.project_task_id) 
and [user].user_id = timesheet.user_id 
and timesheet.user_id =30 
group by    DatePart(week, timesheet_date),
            timesheet.timesheet_date,
            timesheet.start_time,
            timesheet.end_time,
            timesheet.timesheet_status_id,
            timesheet.is_deleted,
            timesheet.created_by,
            timesheet.modified_by,
            timesheet.created_date,
            task.name,
            project.name,
            [user].first_name,
            [user].last_name

Upvotes: 1

Related Questions