We help IT Professionals succeed at work.

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

Murray Brown
Murray Brown asked
on
102 Views
Last Modified: 2015-08-17
Hi
I am trying to get a distinct list of items in one of the columns of a query using the following SQL but get the error
"Incorrect syntax near ')'"

	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')) 

Open in new window

Comment
Watch Question

Ed DreddIT Officer
CERTIFIED EXPERT

Commented:
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')))
Ed DreddIT Officer
CERTIFIED EXPERT

Commented:
Btw "["  and "]"  is not really needed.. i guess.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks. I get the error "Incorrect syntax near the keyword 'From'"
Ed DreddIT Officer
CERTIFIED EXPERT

Commented:
"From" cannot be use as parameter or variable.. that is reserved word.. let replace as From1 or something else
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
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')))
Ed DreddIT Officer
CERTIFIED EXPERT

Commented:
try using notepad++, later you can see if any unmatch or uneven tags. paste the query to notepad++ and then you will see
Ed DreddIT Officer
CERTIFIED EXPERT

Commented:
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'))
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi. Sorry but that still doesn't work
Ed DreddIT Officer
CERTIFIED EXPERT

Commented:
ok ...its 12am here... hope you can solve this soonest..bye.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much so far
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks a lot for the help
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks Paul. I appreciate you taking the time out to help me further
Ed DreddIT Officer
CERTIFIED EXPERT

Commented:
Thanks Paul...
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.