asked on

Subqueries with Aggregates and Constants

Problem: Need to transform data from horizontal to vertical. IE

Part Date Defect1 Defect2
123 5/1/2009 5 6

To

Part Date Defect DefectTotal
123 5/1/2009 Defect1 5
123 5/1/2009 Defect2 6

I am pretty sure this needs to be done with a sub query and I am not sure how to create it.

I have attached a snip of code. Can you tell me where to enter the subquery to Keep updated the Fields Defect and DefectTotal?

Thanks

Select 'ColdSpots' As Defect,Sum(ColdSpot) as DefectTotal, 
	From SCRAP
Where Dt between '5/1/2009' and '5/20/2009'

Open in new window

chapmandew

how many columns total do you have? ie Defect1, Defect2, etc.

Jlucht

ASKER

15 defects in total

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jlucht

ASKER

That is the code that I need to do what I need. One more question. How would I insert that record set into a table.

Below is the code that I used to do it. I had to change a couple of things.

Thanks ALOT!!!

Joe


declare @date1 datetime, @date2 datetime
select @date1 = '5/1/2009', @date2 = '5/20/2009'
 
select Distinct partnum, defect = 'Holes' , defecttotal = Sum(Hole)
From SCRAP
Where Dt between @date1 and @date2
Group By Scrap.PartNum
UNION ALL
select Distinct partNum, defect = 'ColdSpot' , defecttotal = Sum(ColdSpot)
From SCRAP
Where Dt between @date1 and @date2
Group By Scrap.PartNum

Open in new window

Jlucht

ASKER

Thanks for everything