Whats wrong with SQL statement?

[gengel]

Table 1 (UNITS) ScanID ScanCount OrderID SerialNo ScanStatus ProductionNo 1981 1294 2 147639NZ301774 SHIPPED 10007627289 There are 1300 SerialNo's for ProductionNo That will end with status 'SHIPPED' Table 2 (Unit_Logs) LogID SerialNo ProductionNo Old_Status New_Status 1 147639NZ301774 10007627289 PACKED SHIPPED There will be 1300 records with status changes from PACKED to SHIPPED Needed RESULT: ProductionNo PACKED SHIPPED 10007627289 1300 1300 This is what I have tried: SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED, COUNT(UNIT_Logs.SerialNo) AS PACKED FROM UNITS JOIN UNIT_Logs ON UNITS.ProductionNo = UNIT_Logs.ProductionNo GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status HAVING (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED') I added 1 log entry to the table, but the query returns nothing. Think might need sub query, but not sure where to go from here.

[Jorgen Andersson]

HAVING is only meant to be used with aggregate functions. Try something similar to this:

SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status

<edit>fixed layout</edit>

"When did ignorance become a point of view" - Dilbert

modified on Wednesday, March 3, 2010 3:54 AM

[i j russell]

SELECT UNITS.ProductionNo,
COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS
JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo

[gengel]

Hi, Thanks for the replies. This query only seems to make a 1 to 1 join. So if there is only one log entry it returns both SHIPPED and PACKED as 1 instead of 1294. ProductionNo SHIPPED PACKED 10007627289 1 1 if i remove: WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED') ProductionNo SHIPPED PACKED 10007627289 1294 1294 I tried this mess. Will try a few more options with this. SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED FROM UNITS WHERE UNITS.Scanstatus='SHIPPED' AND (SELECT COUNT(UNIT_Logs.SerialNo) AS PACKED FROM UNIT_Logs WHERE UNIT_Logs.Old_Status='PACKED') GROUP BY UNITS.ProductionNo

[i j russell]

Try this one.

SELECT u.ProductionNo,
u.SHIPPED,
l.PACKED
FROM
(
SELECT ProductionNo,
COUNT(SerialNo) AS SHIPPED
FROM UNITS
WHERE ScanStatus = 'SHIPPED'
GROUP BY ProductionNo
) u
LEFT OUTER JOIN
(
SELECT ProductionNo,
COUNT(SerialNo) AS PACKED
FROM UNIT_Logs
WHERE Old_Status = 'PACKED'
GROUP BY ProductionNo
) l
ON l.ProductionNo = u.ProductionNo

[Fixed typo]

modified on Wednesday, March 3, 2010 7:56 AM

[gengel]

Wow. did not even know you could do this. I get this error though: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'FROM'. Msg 102, Level 15, State 1, Line 19 Incorrect syntax near 'l'. I am going to break it down into components to make sure the parts work and then try and connect it back together again. Thanks

[gengel]

Sorry found the problem. , before the FROM in the first bracket. It works perfectly. Thanks for the help.

[i j russell]

You're welcome.