# Group by BASE PART

How can I get a sum total of labor hours for each part and operation for each BASE part?

I do have BASE parts available in another table if this will help.

``````    SELECT
ct.[BASE]
,ct.[BASEDescription]

,ct.[PART_NO]
,ct.[M]
,ct.[OP]
,ct.[PIECE]
,ct.[QTY]
,ct.[PART_ID]
,ct.[DESCRIPTION]

,tt.LABOR_HOURS
,tt.LABOR_COST
FROM [DATABASE].[dbo].[CarrieTbl] ct inner join DATABASE2.dbo.TT_HOUR_DETAILS tt
ON ct.[PART_NO]=tt.PART_ID
``````
Microsoft SQL Server 2005

Last Comment
Annette Wilson, MSIS

8/22/2022 - Mon
bcnagel

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
``````

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Annette Wilson, MSIS

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.
Annette Wilson, MSIS

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.
SOLUTION
bcnagel

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Annette Wilson, MSIS