• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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

0
Jlucht
Asked:
Jlucht
  • 3
  • 2
1 Solution
 
chapmandewCommented:
how many columns total do you have?  ie Defect1, Defect2, etc.
0
 
JluchtAuthor Commented:
15 defects in total
0
 
chapmandewCommented:
easy way to do it in 2000 (2005 would be different)
declare @date1 datetime, @date2 datetime
select @date1 = '5/1/2009', @date2 = '5/20/2009'

select part, date, defect = 'Defect1' , defecttotal = Defect1
From SCRAP
Where Dt between @date1 and @date2
UNION ALL
select part, date, defect = 'Defect2' , defecttotal = Defect2
From SCRAP
Where Dt between @date1 and @date2

....et.al.
0
 
JluchtAuthor Commented:
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

0
 
JluchtAuthor Commented:
Thanks for everything
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now