Help With Sub-Query Written in the FROM Clause

[RunTime91]

Hello ~ Hello

I am trying to get the following Sub-Query to work in SQL Server (Although both queries were brought to SQL from Access)

Both Queries work independantly with the second query derriving its records and field names from the first query

Here is the first query

SELECT DISTINCT a.ABCID, RprtDate, TimeStmp, LOCNUM, IIf(Left([DISTLABEL],4)='ABCD','ABCD',[DISTLABEL])
AS Section, IIf(Left([DISTLABEL],4)='DOCK','Phone','Fax')
AS Fax, a.BEGINDATE, a.CRADDLEDATE, a.GROUP
FROM dbo.MyTable a
WHERE GROUP IN ('DEFG','ABCD')

Here is the second query which derrives its values from the above query

SELECT a.RprtDate, a.Section, Count(a.TimeStmp) AS Total, a.TimeStmp
FROM dbo.MyTable a
GROUP BY a.RprtDate, a.Section, a.TimeStmp

And here is my attempt to Nest the first query in the FROM Clause of the second query

SELECT a.RprtDate, a.Section, Count(a.TimeStmp) AS Total, a.TimeStmp
       FROM (SELECT DISTINCT a.[ABCID], RprtDate, TimeStmp, [LOCNUM], IIf(Left([DISTLABEL],4)='ABCD','ABCD',[DISTLABEL])
              AS Section, IIf(Left([DISTLABEL],4)='DOCK','Phone','Fax')
                     AS Fax, a.[BEGINDATE], a.[CRADDLEDATE], a.[GROUP]
                     FROM dbo.MyTable a
              WHERE [GROUP] IN ('DEFG','ABCD')
       GROUP BY a.RprtDate, a.Section, a.TimeStmp)

The error I can't seem to get rid of is the: Incorrect syntax near ')' which occurs on the last ')' of the query after TimeStmp in the GROUP Clause

Usually these errors are easy to fix - but this is my first attempt at building a Sub-Query and so, yeah, not having any luck at all making the little red squiggly line go away.

Thanks as always...

[Micron]

don't know if it will run even if I'm right, but it looks to me like you have an un-paired parenthesis at the end
a.TimeStmp)

[RunTime91]

Hey Micron ~

Thanks for stepping into this one - I'm at my wits end - That Parens actually closes the Sub-Query

If I move it (the parens) up to the WHERE Clause - then the red squiggly goes under 'GROUP' in the GROUP Clause

btw I renamed GROUP to GrCode thinking that maybe that was the issue - nothin'

[RunTime91]

I just tried moving the closing parens from the GROUP BY Clause to the WHERE Clause -
As it seems if the closing parens on the Sub-Query is on the GROUP BY Clause then it might be looking to the SELECT Fields of the Sub-Query
For the Fields to GROUP BY -

But now I am dealing with a syntax error near the Keyword GROUP...

jsmh...

[Micron]

Yeah I missed one. Now on to another comment that's probably just as useful. I don't claim to be a subquery expert (usually win, but takes a lot of effort).
I can't recall one ever being nested inside of the first FROM clause. Then again, I've only ever used them to get the immediate prior or next record in the same domain. Also, when the subquery mines the same domain, one of them must be aliased. You are using "a" for both the main and sub. Did you ever review
http://allenbrowne.com/subquery-01.html

[ArviLaanemets]

I did make your query from 1st post more readable

SELECT 
    a.RprtDate, a.Section, Count(a.TimeStmp) AS Total, a.TimeStmp
FROM 
   (
      SELECT DISTINCT 
         a.[ABCID], RprtDate, TimeStmp, [LOCNUM], 
         IIf(Left([DISTLABEL],4)='ABCD','ABCD',[DISTLABEL]) AS Section, 
         IIf(Left([DISTLABEL],4)='DOCK','Phone','Fax') AS Fax, 
         a.[BEGINDATE], a.[CRADDLEDATE], a.[GROUP]
      FROM 
         dbo.MyTable a
      WHERE 
         a.[GROUP] IN ('DEFG','ABCD')
      GROUP BY 
         a.RprtDate, a.Section, a.TimeStmp
  )

As source you have a query with a lot of columns returned, no aggregate fields, and with a GROUP BY clause. There is no way this query will run without error!
Then you use this source in query, where you have aggregate field + 3 other fields - and no GROUP BY clause. Even with correct source, this syntax also returns an error for sure!

It looks like you put the where clause into wrong query! And you refer to aliases used inside of source query in final query - you have to give separate alias for source query like Micron did advice.

But I don't see any difference in results between what you attempted, and a simple aggregate query

SELECT
   a.RprtDate, a.Section, Count(a.TimeStmp) AS Total, a.TimeStmp
FROM 
   dbo.MyTable a
WHERE 
   [GROUP] IN ('DEFG','ABCD')
GROUP BY 
   a.RprtDate, a.Section, a.TimeStmp

[CJ_London]

you haven't aliased your subquery

SELECT a.RprtDate, a.Section, Count(a.TimeStmp) AS Total, a.TimeStmp
FROM (SELECT.....)

So either remove the a from the first select

SELECT RprtDate, Section.....

or alias the subquery

FROM dbo.MyTable a
WHERE [GROUP] IN ('DEFG','ABCD')
GROUP BY a.RprtDate, a.Section, a.TimeStmp) a

[RunTime91]

Micron ~ AvrilLaanemets & Ajax

Thank You~!!!

This was my first attempt at creating a Sub-Query. Persistance + some expert assistance (Thank You - Thank You!) Got it done!!

Notes:
Closing the Sub-Query @ the end of the WHERE Clause was the correct call.
Once I realized the Sub-Query was actually just a version of the source table
I understood the need to give it, its own alias
Thus, I simply gave the entire Sub-Query and all the column references to it a different alias
And Boom!

Below is the solution which produces the correct results and lighting fast!!

SELECT a.RprtDate, a.Section, Count(a.TimeStmp) AS Total, a.TimeStmp
 
 FROM (SELECT DISTINCT b.RprtDate, TimeStmp, [LOCNUM], IIf(Left([DISTLABEL],4)='ABCD','ABCD',[DISTLABEL])
 AS Section, IIf(Left([DISTLABEL],4)='DOCK','Phone','Fax')
 AS Fax, b.[BEGINDATE], b.[CRADDLEDATE], b.[GrCode]
 FROM dbo.MyTable b
 WHERE [GrCode] IN ('DEFG','ABCD')) a
 
GROUP BY a.RprtDate, a.Section, a.TimeStmp