SQL Get a distinct list of items from a column in a query

Just re-arrange to analyse easier; i think the first para should have "(" after DISTINCT. and closing ")"  at the end after last ")" , just make sure it have enough pair..


Select Distinct   ( [Performance].[Hole Number]
From (SELECT [Performance].Description, [Performance].[Shaft], [Performance].[Hole Number],  [Performance].[SAP Number], [vSAP_Shafts].[Rate] As Rate,
Min([Performance].[Drilled From]) AS [From],
Max([Performance].[Drilled To]) AS [To],
Sum([Performance].[Drilled Total]) AS Advance,
Sum([Performance].[Concrete Redrill]) AS ReDrill,
Sum([Performance].Setup) AS Setups,
Sum([Performance].[DWR Hours]) AS DWR,
Sum([Performance].[Standing Time]) AS [Standing Time],
Sum([Performance].[Grout Hours]) AS [Grout Hours],
Sum([Performance].[Transport Hours]) as [Transport Hours],
Sum([Performance].[Photographic Survey]) as [Photographic Survey],
Sum([Performance].[Day Rate]) as [Day Rate],
Sum([Performance].[Blank Cap]) as [Blank Cap],
Sum([Performance].[Mobilise]) as [Mobilise]
FROM [Performance] Left Join [vSAP_Shafts]  On ([Performance].Shaft = [vSAP_Shafts].Shaft
And [Performance].[Description] = [vSAP_Shafts].[Description])
Where Date >= '16 Aug 2015'
And Date <= '16 Aug 2015'  
GROUP BY [Performance].[Hole Number], [Performance].Description, [Performance].[SAP Number], [vSAP_Shafts].[Rate], [Performance].Shaft  
HAVING  [Performance].[Shaft] IN ('kopanang air')))

Btw "["  and "]"  is not really needed.. i guess.

Thanks. I get the error "Incorrect syntax near the keyword 'From'"

"From" cannot be use as parameter or variable.. that is reserved word.. let replace as From1 or something else

I still get that error if I change From to DFrom as below

Select Distinct   ( [Performance].[Hole Number]
 From (SELECT [Performance].Description, [Performance].[Shaft], [Performance].[Hole Number],  [Performance].[SAP Number], [vSAP_Shafts].[Rate] As Rate,
 Min([Performance].[Drilled From]) AS [DFrom],
 Max([Performance].[Drilled To]) AS [DTo],
 Sum([Performance].[Drilled Total]) AS Advance,
 Sum([Performance].[Concrete Redrill]) AS ReDrill,
 Sum([Performance].Setup) AS Setups,
 Sum([Performance].[DWR Hours]) AS DWR,
 Sum([Performance].[Standing Time]) AS [Standing Time],
 Sum([Performance].[Grout Hours]) AS [Grout Hours],
 Sum([Performance].[Transport Hours]) as [Transport Hours],
 Sum([Performance].[Photographic Survey]) as [Photographic Survey],
 Sum([Performance].[Day Rate]) as [Day Rate],
 Sum([Performance].[Blank Cap]) as [Blank Cap],
 Sum([Performance].[Mobilise]) as [Mobilise]
 FROM [Performance] Left Join [vSAP_Shafts]  On ([Performance].Shaft = [vSAP_Shafts].Shaft
 And [Performance].[Description] = [vSAP_Shafts].[Description])
 Where Date >= '16 Aug 2015'
 And Date <= '16 Aug 2015'  
 GROUP BY [Performance].[Hole Number], [Performance].Description, [Performance].[SAP Number], [vSAP_Shafts].[Rate], [Performance].Shaft  
 HAVING  [Performance].[Shaft] IN ('kopanang air')))

try using notepad++, later you can see if any unmatch or uneven tags. paste the query to notepad++ and then you will see

try this... i just found another FROM after  Sum([Performance].[Mobilise]) as [Mobilise]
  FROM [Performance]

just put another ")" after mobilise.. here the refined code..; hope this help



Select Distinct   ( [Performance].[Hole Number]
 FROM (SELECT [Performance].Description, [Performance].[Shaft], [Performance].[Hole Number],  [Performance].[SAP Number], [vSAP_Shafts].[Rate] As Rate,
 Min([Performance].[Drilled From]) AS [DFrom],
 Max([Performance].[Drilled To]) AS [DTo],
 Sum([Performance].[Drilled Total]) AS Advance,
 Sum([Performance].[Concrete Redrill]) AS ReDrill,
 Sum([Performance].Setup) AS Setups,
 Sum([Performance].[DWR Hours]) AS DWR,
 Sum([Performance].[Standing Time]) AS [Standing Time],
 Sum([Performance].[Grout Hours]) AS [Grout Hours],
 Sum([Performance].[Transport Hours]) as [Transport Hours],
 Sum([Performance].[Photographic Survey]) as [Photographic Survey],
 Sum([Performance].[Day Rate]) as [Day Rate],
 Sum([Performance].[Blank Cap]) as [Blank Cap],
 Sum([Performance].[Mobilise]) as [Mobilise])
 
 FROM [Performance]
 Left Join [vSAP_Shafts]  On ([Performance].Shaft = [vSAP_Shafts].Shaft
 And [Performance].[Description] = [vSAP_Shafts].[Description])
 Where Date >= '16 Aug 2015'
 And Date <= '16 Aug 2015'  
 GROUP BY [Performance].[Hole Number], [Performance].Description, [Performance].[SAP Number], [vSAP_Shafts].[Rate], [Performance].Shaft  
 HAVING  [Performance].[Shaft] IN ('kopanang air'))

Hi. Sorry but that still doesn't work

ok ...its 12am here... hope you can solve this soonest..bye.

Thanks very much so far

Thanks a lot for the help

No problem.

I have just noticed that your HAVING clause should be treated as an additional condition in the WHERE clause.

HAVING is performed AFTER the group by, and designed to allow filtering on aggregate calculations. What you are currently using as a having clause does not have to wait until after grouping is performed.

Thanks Paul. I appreciate you taking the time out to help me further

Thanks Paul...