I am getting an error message for this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I am getting an error message for this query

Author

Topic

Oz7
Starting Member

8 Posts

Posted - 2007-12-13 : 18:12:01

SELECT T1.lnumber AS LOT_Number,(T2.time_out - T1.time_in) AS Duration,
((DATEPART (hour, (T2.time_out-T1.time_in)) *60) + DATEPART (minute, (T2.time_out-T1.time_in))) AS Minutes, COUNT(DISTINCT T2.test_desc) AS Number_of_Process

FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber

WHERE T2.test_desc = 'Shipping' AND T1.test_desc = 'Receiving'

I have an error message that says:

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

If I take this line out: COUNT(DISTINCT T2.test_desc) AS Number_of_Process
The query runs ok.

Also if I run [COUNT(DISTINCT T2.test_desc) AS Number_of_Process] in:

SELECT COUNT(DISTINCT T2.test_desc) AS Number_of_Process, T2.lnumber AS Lot_number
FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber
GROUP BY T2.lnumber

It will also give me result.
Please help....
Thanks for your time

Oz7
Starting Member

8 Posts

Posted - 2007-12-13 : 18:19:35

Sorry I think this topic should belong to "New to SQL Server" sub forum.
I wish I could move this threat there.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 18:24:18

I moved the thread to the requested forum.

In order for us to help you with your problem, you need to provide a data example of what you are trying to do. There is probably a beter approach to the problem than what you are attempting to do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-12-13 : 18:24:20

You are trying to do a query with an aggregate function and not grouping appropriately, hence the error.

How does number of processes are you trying to count relate to the query? It would appear you just want the number of test_descs for t2, which according to your where clause would be 0, 1 or 2. Would a correlated sub-query be more appropriate in place of your count?

Actually I'm not sure what your question is really so can you be more specific?!

Oz7
Starting Member

8 Posts

Posted - 2007-12-13 : 18:42:42

Thanks for moving my threat to the right sub forum tkizer.

Here is my situation:
I have a table called Results and the attributes are lnumber, test_desc, time_in, & time_out.

For each lnumber there will be 3 or more test_desc: one of them is receiving and another one is shipping and the rest can be any other test processes we have. For each of the test_desc there is time_in (when the package first enter the process and time_out when the package is done with that particular process.

What I am trying to do is to display the length of time it takes for a package since it arrives at our facility from customer (receiving's time_in) until it goes back to the customer's site (shipping time_out) -- this step is already done by using "(T2.time_out - T1.time_in) AS Duration"

The next one that I want to calculate is the average time it takes for each processes. So I want to divide "Duration" by the number of processes that each package goes through.

That is why I added:
COUNT(DISTINCT T2.test_desc) AS Number_of_Process
Simply to find out how many processes one lnumber goes through, because not every lnumber goes through the same amount of processes.
There can be as many as 23 processes that an lnumber can goes through before it was sent back to the customer.

I don't know if I explain it good enough or not.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 18:52:11

Please post a data example of your data with the expected result set.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Oz7
Starting Member

8 Posts

Posted - 2007-12-13 : 19:28:22

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 20:06:30

So you've provided your output, but we also need to see what the data looks like in the tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Oz7
Starting Member

8 Posts

Posted - 2007-12-13 : 20:33:07

tkizer, here is what my data looks like:

lnumber | test_desc | time_in | time_out
721 | Receiving | 2007-02-12 08:00:00.000 | 2007-02-12 13:45:00.000
721 | Incoming QA | 2007-02-12-13:46:00.000 | 2007-02-12 13:50:00.000
721 | Visual Check | 2007-02-26 12:45:00.000 | 2007-02-26 13:20:00.000
...
721 | Shipping | 2007-02-26 13:44:00.000 | 2007-02-26 13:45:00.000
A21 | Receiving | 2007-05-11 08:20:00.000| 2007-05-11 11:11:00.000
....

That's what my data look like.
I think I know what causing my Number_of_Process is always 1, it is because of this clause over here:

WHERE T2.test_desc = 'Shipping' AND T1.test_desc = 'Receiving'

I need that clause so my Minute attribute would work, but at the same time it is filtering my Number_of_Process attribute.
How can I fix this?
Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-14 : 13:58:39

Somthing like this?

DECLARE @Results TABLE (lnumber INT, test_desc VARCHAR(50), time_in DATETIME, time_out DATETIME)

INSERT @Results
SELECT 721, 'Receiving', '2007-02-12 08:00:00.000', '2007-02-12 13:45:00.000'
UNION ALL SELECT 721, 'Incoming QA', '2007-02-12 13:46:00.000', '2007-02-12 13:50:00.000'
UNION ALL SELECT 721, 'Visual Check', '2007-02-26 12:45:00.000', '2007-02-26 13:20:00.000'
UNION ALL SELECT 721, 'Shipping', '2007-02-26 13:44:00.000', '2007-02-26 13:45:00.000'
UNION ALL SELECT 721, 'Receiving', '2007-05-11 08:20:00.000', '2007-05-11 11:11:00.000'


SELECT 
	lnumber,
	SUM
	(
		CASE
			WHEN test_desc = 'Receiving' OR test_desc = 'Shipping'
				THEN DATEDIFF(MINUTE, time_in, time_out)
			ELSE 0
		END
	) AS Minutes,
	COUNT(*) AS Number_of_Process
FROM @Results
GROUP BY lnumber

Oz7
Starting Member

8 Posts

Posted - 2007-12-15 : 21:39:50

Hi Lamprey, thanks for helping me here.
One thing that I notice with the code that you gave me, I would have to enter all the lnumber manually and it's not necessarily INT type. It is just some auto generated number+character so it could be as bad as this: A003648_W and I won't be able to enter it one by one manually because all these data that I have they have been accumulated since year 2001.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-16 : 15:36:48

You don't have to type in anything manually. The DECLARE and INSERT are how we replicate your environment on our machines. He's just showing you an example with the data provided. So all you need to do is run the last query (SELECT) and modify it to your actual object names. If the result set is not correct, then you need to provide different sample data for us that shows the issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources