SQL nested Select....I think?

I have 2 tables UnitProd and Unit.

Unit = unitproductivityid,unitid, unitnumber, fleet
UnitProd = unitproductivityid, day, shipweight, stops

I have multiple units in each table and I am trying to do group by functions to get counts of different things.(The tables have more fields than specified this is just example purposes.)

So basically I have the following:

SELECT
u.[Fleet]
,u.[Unit]
,up.[Day]
,((SUM(up.[Shipment_Weight]))/2000) AS [ShipmentWeight]
,((SUM(up.[Shipment_Weight]))/COUNT(up.[Stops])) AS [ShpmntAvg]
FROM
[dbo].[UnitProductivity] u
INNER JOIN [dbo].[UnitProductivityDetails] up
ON u.UnitProductivityId = up.UnitProductivityId
GROUP BY u.fleet, u.unit

So basically the issue I am having is that some up.[Stops] fields have a 0 in them so I want to exclude these. So basically a unit has 1-30 days no matter what and some of those days have a 0 as开发者_C百科 [Stop] so I want to count(ONLY DAYS with a stop). Would I use a nested select here and how?

Thanks

Unless I am misunderstanding the question, you don't need a nested SELECT.

Just add the following before your GROUP BY:

WHERE up.[Stops] > 0

It doesn't have to be nested, but here's a simple way:

SELECT * FROM Unit WHERE unitproductivityid IN (SELECT unitproductivityid FROM UnitProd WHERE stops > 0) as UP

Good luck!

after your GROUP BY line, add HAVING count(up.[Stops]) > 0

With existing code you can do HAVING clause:

SELECT u.[Fleet] ,u.[Unit] ,up.[Day] ,((SUM(up.[Shipment_Weight]))/2000) AS [ShipmentWeight] ,((SUM(up.[Shipment_Weight]))/COUNT(up.[Stops])) AS [ShpmntAvg] FROM [dbo].[UnitProductivity] u INNER JOIN [dbo].[UnitProductivityDetails] up ON u.UnitProductivityId = up.UnitProductivityId GROUP BY u.fleet, u.unit HAVING(COUNT(up.[Stops]) > 0

More about HAVING clause on: http://www.w3schools.com/sql/sql_having.asp