asked on

Sql error: ...is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone help me with this broken query? I will have about 10 different case statements. Also, the ouput needs to be in hours and minutes.

Thank you,
Jessy

SELECT 
	 CASE
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)
     END ,
	CASE
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)
     END ,
	DATEPART(Week,CT_Date) AS Weekno,
	COUNT(CT_Key) AS NbrOfRecords,	 
	substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5)	
  FROM Cycle_Time 
	GROUP BY DATEPART(Week,CT_Date)

Open in new window

Rajkumar Gs

Give this a try. Let me know

Raj

SELECT  
         CASE 
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time) 
     END , 
        CASE 
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time) 
     END , 
        DATEPART(Week,CT_Date) AS Weekno, 
        COUNT(CT_Key) AS NbrOfRecords,    
        substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5)         
  FROM Cycle_Time  
        GROUP BY DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480,
				DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time),  
				DATEDIFF(mi, Invoice_Time, Manifest_Time),
				DATEPART(Week,CT_Date),
				substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5)

Open in new window

JessyRobinson1234

ASKER

Thank you. Error: Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Rajkumar Gs

Try this

Raj

SELECT  
         CASE 
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time) 
     END Col1 , 
        CASE 
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time) 
     END Col2, 
        DATEPART(Week,CT_Date) AS Weekno, 
        COUNT(CT_Key) AS NbrOfRecords,    
        substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5) AS Col3        
  FROM Cycle_Time  
        GROUP BY Col1, Col2, Weekno, Col3

Open in new window

JessyRobinson1234

ASKER

Msg 207, Level 16, State 1, Line 14
Invalid column name 'Col1'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Col2'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Weekno'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Col3'.

Shannon_Lowder

The columns listed in your group by clause need to match exactly with those same columns in the select clause, instead of "co1" use "CASE
WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480
ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)
END"
---
Shannon lowder
Database Engineer
http://toyboxcreations.net

Rajkumar Gs

Hope this would work

Raj

SELECT   
         CASE  
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)  
     END  ,  
        CASE  
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)  
     END ,  
        DATEPART(Week,CT_Date) AS Weekno,  
        COUNT(CT_Key) AS NbrOfRecords,     
        substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5)         
  FROM Cycle_Time   
        GROUP BY 
	CASE  
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)  
     END, 
		CASE  
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)  
     END, 
	DATEPART(Week,CT_Date), substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5)

Open in new window

JessyRobinson1234

ASKER

that gives me the same error.

JessyRobinson1234

ASKER

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Rajkumar Gs

Try this. using another approach

Raj

SELECT Col1, Col2, Col3, Weekno, COUNT(CT_Key) AS NbrOfRecords  
FROM
(
	SELECT  
         CASE 
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time) 
     END Col1, 
        CASE 
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time) 
     END Col2, 
        DATEPART(Week,CT_Date) AS Weekno, 
        CT_Key,    
        substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5) AS Col3
  FROM Cycle_Time  
) AA
GROUP BY Col1, Col2, Col3, Weekno

Open in new window

8080_Diver

Try the following:

8080_Diver

Danged delayed responses . . . wound up deleting the code. :-/

SELECT Date1,
       Date2,
       COUNT(CT_Key) AS NbrOfRecords,
       Weekno,
       CycleTime
FROM
(
SELECT
       CASE WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1
              THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480
              ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)
       END Date1,
       CASE WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1
              THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480
              ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)
       END Date2,
       DATEPART(Week, CT_Date) AS Weekno,
       substring(CONVERT(VARCHAR(23), CONVERT(DATETIME, AVG(CONVERT(REAL, Cycle_Time))), 121), 12, 5) CycleTime
FROM   Cycle_Time
) Z
GROUP BY Date1,
         Date2,
         Weekno,
         CycleTime

Open in new window

JessyRobinson1234

ASKER

Msg 8120, Level 16, State 1, Line 1
Column 'Cycle_Time.Pull_Compl_Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'CT_Key'.

JessyRobinson1234

ASKER

Above error applied to 8080 Diver's code.

Here's Raj's error:

Msg 8120, Level 16, State 1, Line 1
Column 'Cycle_Time.Pull_Compl_Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Rajkumar Gs

First let me know what is the result of this inner query (not entire query)

Raj

SELECT   
         CASE  
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)  
     END Col1,  
        CASE  
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)  
     END Col2,  
        DATEPART(Week,CT_Date) AS Weekno,  
        CT_Key,     
        substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5) AS Col3 
  FROM Cycle_Time

Open in new window

JessyRobinson1234

ASKER

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

8080_Diver

This should get rid of the CT_Key issue.

SELECT Date1, 
       Date2, 
       COUNT(CT_Key) AS NbrOfRecords, 
       Weekno, 
       CycleTime 
FROM 
( 
SELECT CT_Key,
       CASE WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 
              THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
              ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time) 
       END Date1, 
       CASE WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 
              THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480 
              ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time) 
       END Date2, 
       DATEPART(Week, CT_Date) AS Weekno, 
       substring(CONVERT(VARCHAR(23), CONVERT(DATETIME, AVG(CONVERT(REAL, Cycle_Time))), 121), 12, 5) CycleTime 
FROM   Cycle_Time 
) Z 
GROUP BY Date1, 
         Date2, 
         Weekno, 
         CycleTime

Open in new window

JessyRobinson1234

ASKER

8080 Diver: yes:
Column 'Cycle_Time.CT_Key' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

8080_Diver

Then try this (I forgot to also add the CT_Key to the GROUPING):

SELECT CT_Key,
       Date1,  
       Date2,  
       COUNT(CT_Key) AS NbrOfRecords,  
       Weekno,  
       CycleTime  
FROM  
(  
SELECT CT_Key, 
       CASE WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1  
              THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
              ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)  
       END Date1,  
       CASE WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1  
              THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
              ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)  
       END Date2,  
       DATEPART(Week, CT_Date) AS Weekno,  
       substring(CONVERT(VARCHAR(23), CONVERT(DATETIME, AVG(CONVERT(REAL, Cycle_Time))), 121), 12, 5) CycleTime  
FROM   Cycle_Time  
) Z  
GROUP BY CT_Key,
         Date1,  
         Date2,  
         Weekno,  
         CycleTime

Open in new window

Rajkumar Gs

About this ?

Raj

SELECT Col1, Col2, Col3, Weekno, COUNT(CT_Key) AS NbrOfRecords   
FROM 
( 
        SELECT   
         CASE  
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)  
     END Col1,  
        CASE  
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480  
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)  
     END Col2,  
        DATEPART(Week,CT_Date) AS Weekno,  
        CT_Key,     
        substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5) AS Col3 
  FROM Cycle_Time 
	GROUP BY Cycle_Time.Pull_Compl_Time  
) AA 
GROUP BY Col1, Col2, Col3, Weekno

Open in new window

JessyRobinson1234

ASKER

Msg 8120, Level 16, State 1, Line 1
Column 'Cycle_Time.Pull_Req_Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'Cycle_Time.Pull_Req_Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'Cycle_Time.Pull_Req_Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Rajkumar Gs

If I understood the above mentioned error, query should be like this

Raj

SELECT Col1, Col2, Col3, Weekno, COUNT(CT_Key) AS NbrOfRecords    
FROM  
(  
        SELECT    
         CASE   
         WHEN DATEDIFF(dd, Pull_Compl_Time, Pull_Req_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480   
         ELSE DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)   
     END Col1,   
        CASE   
         WHEN DATEDIFF(dd, Manifest_Time, Invoice_Time) > 1 THEN DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)-480   
         ELSE DATEDIFF(mi, Invoice_Time, Manifest_Time)   
     END Col2,   
        DATEPART(Week,CT_Date) AS Weekno,   
        CT_Key,      
        substring(convert(varchar(23),convert(datetime,avg(convert(real,Cycle_Time))),121),12,5) AS Col3  
  FROM Cycle_Time  
        GROUP BY Pull_Compl_Time, Pull_Req_Time, Manifest_Time, Invoice_Time, CT_Date, CT_Key, Cycle_Time
) AA  
GROUP BY Col1, Col2, Col3, Weekno

Open in new window

JessyRobinson1234

ASKER

Hi Raj,

No more errors, thank you. However, the result set is no longer grouping by week and gives me 244 rows. I should only get 10 rows since there were only 10 distinct weeks.

Jessy

Rajkumar Gs

Can you post the create table script of the table(s) that we use in this query?

Also post your sample data.

This will surely help me to arrive to the solution soon.

Raj

JessyRobinson1234

ASKER

Here you go. Thanks for all your help!

SE [Bus_Ops]
GO
/****** Object:  Table [dbo].[Cycle_Time]    Script Date: 04/06/2010 22:11:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cycle_Time](
	[CT_Key] [int] NULL,
	[CT_Date] [datetime] NULL,
	[Ship_Key] [int] NULL,
	[Trailer_Nbr] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pull_Request] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pull_Req_Time] [datetime] NULL,
	[Pull_Req_Time_Two] [datetime] NULL,
	[Pull_Req_Time_Three] [datetime] NULL,
	[Pull_Req_Time_Four] [datetime] NULL,
	[Pull_Req_Time_Five] [datetime] NULL,
	[Fill_Rate] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Driver_Time] [datetime] NULL,
	[Pull_Compl_Time] [datetime] NULL,
	[Val_Compl_Time] [datetime] NULL,
	[Manifest_Time] [datetime] NULL,
	[Invoice_Time] [datetime] NULL,
	[SED_Start_Time] [datetime] NULL,
	[Trailer_Load_Time] [datetime] NULL,
	[SED_Compl_Time] [datetime] NULL,
	[Driver_Arriv_Time] [datetime] NULL,
	[Driver_Depart_Time] [datetime] NULL,
	[Customs_Time] [datetime] NULL,
	[Border_Depart] [datetime] NULL,
	[Trailer_Arriv_Time] [datetime] NULL,
	[Cycle_Time] [datetime] NULL,
	[Comments] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Closed] [bit] NULL,
	[Rc_PR1_Inv] [bit] NULL,
	[Rc_PR2_Inv] [bit] NULL,
	[Rc_PR3_Inv] [bit] NULL,
	[Rc_PR4_Inv] [bit] NULL,
	[Rc_PR5_Inv] [bit] NULL,
	[Rc_VC_Inc_Pull] [bit] NULL,
	[Rc_VC_Inc_Part] [bit] NULL,
	[Rc_MS_Syst] [bit] NULL,
	[Rc_MS_Incorr] [bit] NULL,
	[Rc_IR_Syst] [bit] NULL,
	[Rc_IR_Incorr] [bit] NULL,
	[Rc_SED_Delay] [bit] NULL,
	[Rc_SED_Incorr] [bit] NULL,
	[Rc_SEDS_Incorr] [bit] NULL,
	[Rc_BA_Traffic] [bit] NULL,
	[Rc_BD_Red_Light] [bit] NULL,
	[File_Name] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[File] [varbinary](max) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Open in new window

data.xlsx

Rajkumar Gs

Well !

Post some sample data & your expected results based on that data.

This will help me to involve into your real situation.

Raj

Rajkumar Gs

Oh... I just seen your attachment.
Hope it contains sample data & expected result.

I am trying to convert it to *.xls format...

Raj

Rajkumar Gs

I checked the Excel. I think it is the result of my query.

Just post your sample data from 'Cycle_Time' table and your expected output based on that data.

This will help me.
Raj

JessyRobinson1234

ASKER

Raj, This would my deisred outcome:

Week      Nbr Trailers      CT Avg      CT Target CT STDEV

5      24      3:38      3:00      3:57
7      11      4:02      3:00      5:38
8      16      1:52      3:00      4:17
9      18      1:56      3:00      5:34
10      24      2:51      3:00      8:28
11      30      4:17      3:00      10:29
12      22      4:36      3:00      2:34
13      17      4:08      3:00      2:41
Grand Total      163      3:24      3:00      6:33

JessyRobinson1234

ASKER

I attached the file that has the raw data and the desired outcome in excel. Now I need to have all of this accomplished in MS SQL 2005. I hope this helps.
Jessy-ElPaso--6-.xlsx

Rajkumar Gs

Good! Will be back after sometime.

Raj

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JessyRobinson1234

ASKER

I get an error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Cycle_Time'

JessyRobinson1234