none
How to select distinct data from a column while also needing to aggregate data from the non-distinct columns? RRS feed

  • Question

  • Hello, what I'm attempting to do is find the total for fcompqty for each fdate. I want to just to see each distinct fdate and the total of ALL of the  fcompqty as my final result. What I have so far is my query will get the unique dates but only quantity from those specific dates. My end goal is to get  # of Distinct fdate days/ Total fcompqty , to determine an efficiency score. 

    Select *
    From
    
    (Select  fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ) AS RowNumber
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0) V
    Where V.RowNumber = 1

    Thursday, December 20, 2018 6:23 PM

Answers

  • It would help us help you if you gave us sample data and the result you would want from that sample data.  I'm not quite sure whether you want the total for fcompqty for each date  or the overall total of fcompqty.  So the following query will give you both

    Select *
    From
    
    (Select  fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ,
    SUM(fcompqty) Over(PARTITION BY fdate) As SumForThisDate,
    SUM(fcompqty) Over() As SumForAllDates,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ) AS RowNumber
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0) V
    Where V.RowNumber = 1

    And the number of Distinct fdate days / Total of fcompqty is just

    Select  Cast(Count(Distinct fdate) As float)/ SUM(fcompqty)
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0
    Tom
    • Marked as answer by David9501 Thursday, December 20, 2018 7:35 PM
    Thursday, December 20, 2018 6:57 PM

All replies

  • It would help us help you if you gave us sample data and the result you would want from that sample data.  I'm not quite sure whether you want the total for fcompqty for each date  or the overall total of fcompqty.  So the following query will give you both

    Select *
    From
    
    (Select  fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ,
    SUM(fcompqty) Over(PARTITION BY fdate) As SumForThisDate,
    SUM(fcompqty) Over() As SumForAllDates,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ) AS RowNumber
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0) V
    Where V.RowNumber = 1

    And the number of Distinct fdate days / Total of fcompqty is just

    Select  Cast(Count(Distinct fdate) As float)/ SUM(fcompqty)
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0
    Tom
    • Marked as answer by David9501 Thursday, December 20, 2018 7:35 PM
    Thursday, December 20, 2018 6:57 PM
  • It would help us help you if you gave us sample data and the result you would want from that sample data.  I'm not quite sure whether you want the total for fcompqty for each date  or the overall total of fcompqty.  So the following query will give you both

    Select *
    From
    
    (Select  fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ,
    SUM(fcompqty) Over(PARTITION BY fdate) As SumForThisDate,
    SUM(fcompqty) Over() As SumForAllDates,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ) AS RowNumber
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0) V
    Where V.RowNumber = 1

    And the number of Distinct fdate days / Total of fcompqty is just

    Select  Cast(Count(Distinct fdate) As float)/ SUM(fcompqty)
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0
    Tom

    Thank you,Tom. This is exactly what I was looking for.
    Thursday, December 20, 2018 7:36 PM