Answered by:
How to select distinct data from a column while also needing to aggregate data from the nondistinct columns?
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='IT5780000' and fcompqty<>0) V Where V.RowNumber = 1
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='IT5780000' 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='IT5780000' and fcompqty<>0
Tom Marked as answer by David9501 Thursday, December 20, 2018 7:35 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='IT5780000' 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='IT5780000' and fcompqty<>0
Tom Marked as answer by David9501 Thursday, December 20, 2018 7:35 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='IT5780000' 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='IT5780000' and fcompqty<>0
Tom
Thank you,Tom. This is exactly what I was looking for.