Results 1 to 9 of 9

Thread: Sum(case)

  1. #1
    Join Date
    Jan 2009
    Location
    Chicago
    Posts
    11

    Unanswered: Sum(case)

    Hi everyone,
    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

    FROM
    tbl_Assembly_Holds
    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
    WHERE
    tbl_Assembly_Holds.Record_Date BETWEEN '8/06/2009' AND '8/10/2009' AND tlkp_Defects.Defect_Title = 'Pin Hole'
    GROUP BY
    tlkp_Defects.Defect_Title, tlkp_Disposition.Disposition_Description;

    I only want to see one instance of "Pin Hole" , not every time it appears.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You will get one instance of pin hole for every description.If you want something else, could you post some results from this query (enough to illustrate) and what you would like instead?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Location
    Chicago
    Posts
    11

    Exclamation Sum(case

    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.
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please type out what you want to see instead of those two lines.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2009
    Location
    Chicago
    Posts
    11

    Sum(case)

    Look at lines 4 and 22. That occurs twice between 8/24/2009 and 8/26/2009. I want it to show once but Count "Scrap" total as 9.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please dude - just type out what you want. Don't describe it and make me work at figuring out what you want - just type out what you want the result to be instead of the first screen shot.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  8. #8
    Join Date
    Jan 2009
    Location
    Chicago
    Posts
    11

    Sum(case)

    I removed them and now SCRAP counts are under "Scrap" and "FIxedOnLine". My number is way off. It shows 2090 Pin Holes in 6 days, that can't be correct.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I take it by that then you don't actually know what the result should be?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •