# How to select distinct data from a column while also needing to aggregate data from the non-distinct 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
where fjobno='IT578-0000' and fcompqty<>0) V
Where V.RowNumber = 1```

Thursday, December 20, 2018 6:23 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
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)
where fjobno='IT578-0000' and fcompqty<>0```
Tom
• Marked as answer by 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
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)
where fjobno='IT578-0000' and fcompqty<>0```
Tom
• Marked as answer by 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
```Select  Cast(Count(Distinct fdate) As float)/ SUM(fcompqty)