I have a query that is doing what I want for the most part however, one column I would like to aggregate the Defect_Title Column, I have to add more data to it from other outside tables. Can someone tell if this is possible? Here's my example:
SELECT tlkp_Disposition.Disposition_Description, count(*) AS Disposition_Count, tlkp_Defects.Defect_Title,
SUM(case tlkp_Disposition.Disposition_ID when 1 then tlkp_Defects.Defect_ID else 0 end) FixedOnLine,
SUM(case tlkp_Disposition.Disposition_ID when 2 then tlkp_Defects.Defect_ID else 0 end) Repair,
SUM(case tlkp_Disposition.Disposition_ID when 3 then tlkp_Defects.Defect_ID else 0 end) Scrap
join tlkp_Disposition on tbl_Assembly_Holds.Disposition_ID = tlkp_Disposition.Disposition_ID
join tbl_Assembly_Hold_Defects on tbl_Assembly_Hold_Defects.TagNumber = tbl_Assembly_Holds.TagNumber
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
tbl_Assembly_Holds.Record_Date BETWEEN '8/06/2009' AND '8/10/2009' AND tlkp_Defects.Defect_Title = 'Pin Hole'
I only want to see one instance of "Pin Hole" , not every time it appears.
I attached a screenshot by Word Doc. The problem is: When I ran by date range, if that defect occured multiple times in that range then, it shows multiple times. I want the Defects to aggregate as well as the Dispositions to aggregate.
From you first screen shot, you have two different Disposition_Description associated with Pin Hole and, as pootle flump stated, the GROUP BY will give you a row for each distinct combination of the non-summarized data. In this case "Pin Hole / Fixed On Line" and "Pin Hole / Repair".
If you just want one line with Pin Hole that lists how many are in FixedOnline, Repair and Scrap status, just remove the Disposition_Description from the Select and Group By.