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

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

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ed DreddCommented:
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')))
0
Ed DreddCommented:
Btw "["  and "]"  is not really needed.. i guess.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. I get the error "Incorrect syntax near the keyword 'From'"
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Ed DreddCommented:
"From" cannot be use as parameter or variable.. that is reserved word.. let replace as From1 or something else
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor 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')))
0
Ed DreddCommented:
try using notepad++, later you can see if any unmatch or uneven tags. paste the query to notepad++ and then you will see
0
Ed DreddCommented:
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'))
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Sorry but that still doesn't work
0
Ed DreddCommented:
ok ...its 12am here... hope you can solve this soonest..bye.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much so far
0
PortletPaulfreelancerCommented:
Why are you running a multiple aggregations to get your distinct list?

This is your current query with some syntax corrections and re-formatted (to my preference)
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] >= '20150816' -- don't use dd mmm yyyy format >> '16 Aug 2015'
      AND [Date] < '20150817' -- notice! "less than the next day"
      GROUP BY [Performance].[Hole Number]
             , [Performance].Description
             , [Performance].[SAP Number]
             , [vSAP_Shafts].[Rate]
             , [Performance].Shaft
      HAVING [Performance].[Shaft] IN ('kopanang air')
) AS derived

Open in new window

You could achieve the same result with far less overhead like this:
SELECT
      [Performance].[Hole Number]
FROM [Performance]
LEFT JOIN [vSAP_Shafts]
      ON ([Performance].Shaft = [vSAP_Shafts].Shaft
      AND [Performance].[Description] = [vSAP_Shafts].[Description])
WHERE [Date] >= '20150816' -- don't use dd mmm yyyy format >> '16 Aug 2015'
AND [Date] < '20150817' -- notice! "less than the next day"
GROUP BY
      [Performance].[Hole Number]
HAVING [Performance].[Shaft] IN ('kopanang air')

Open in new window

Please note that you NEVER need "select distinct" when you are doing group by in a single query. GROUP BY forms rows that are unique for the columns you specify in that clause, so it is utterly redundant to then ask for then to be distinct because they already are.

Other notes:
---
'16 Aug 2015'
the only safe date literal formats in SQL Server are YYYYMMDD or YYYY-MM-DDThh:mm:ss[.00000]
so the unsafe date literal should be changed to '20150816'

---
Date = ... curiously you use the square brackets a lot but not around the column [Date], you should because that column name conflicts with a TSQL keyword.

---
WHERE [Date] >= '20150816' AND [Date] <= '20150816'
?
IF the column [Date] only holds date and not date/time, then why not just use
WHERE [Date] = '20150816'

If the column [Date] does hold times other than 00:00:00 then the most accurate method for selection a date range is to use >= with <  like this:

WHERE [Date] >= '20150816' AND [Date] < '20150817' -- notice! "less than the next day"

this method ensures you get all data for '20150816' regardless of the time precision of the column
please see: "Beware of Between"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks a lot for the help
0
PortletPaulfreelancerCommented:
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.
1
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks Paul. I appreciate you taking the time out to help me further
0
Ed DreddCommented:
Thanks Paul...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.