Group by BASE PART

Hi technette.

If each PART_NO corresponds to one and only one BASE, how about trying something like this:

;with cte_Totals as
	(
	select ct.[BASE], ct.[PART_NO], sum(tt.LABOR_HOURS) as TotalLaborHours, null as TotalOP
		from [DATABASE].[dbo].[CarrieTbl] ct
		inner join DATABASE2.dbo.TT_HOUR_DETAILS tt
		on	ct.[PART_NO] = tt.PART_ID;
		group by ct.[BASE], ct.[PART_NO]

	union

	select ct.[BASE], null as PART_NO, null as TotalLaborHours, sum(ct.[OP]) as TotalOP
		from [DATABASE].[dbo].[CarrieTbl] ct
		inner join DATABASE2.dbo.TT_HOUR_DETAILS tt
		on	ct.[PART_NO] = tt.PART_ID;
		group by ct.[BASE]
	)

select *
	from cte_Totals t
	order by BASE, PART_NO

Select allOpen in new window

You might have to adjust things to get it working, but hopefully the general idea makes sense.

Mark,
Thank you!  The query works well.  There no way to get the descriptions in this?

If not, I can create a temp table, add a description column and update it.

Thank you for responding bcnagel.

I'm getting an error on the second part of the query.

Operand data type varchar is invalid for sum operator   "sum(ct.[OP])" - this is varchar.

What if each part has multiple base?

So, for each base, the part may be in one base and be in another base.

Thank you!!