SQL GROUP BY error

[Danimoth]

I am trying to run a query in a SQL database, and for some reason I cannot use GROUP BY conditions at all, or the aggregate functions.

Dim CLin(1 To 1) As String
    CLin(1) = "select TMRA, first(TSERIALNO) from fOWSerialNo group by TMRA"
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=###;Description=###;UID=###;PWD=###;APP=2007 Microsoft Office system;WSID=###;DATABASE=###;Network=###" _
        ), Array("SOCN")), Destination:=Range("$B$1")).QueryTable
        .CommandText = CLin()
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_AQAMAN2K"
        .Refresh BackgroundQuery:=False
    End With

Changing the database itself isn't an option. There is a lot more I need to do with this, (the Command Text will end up being many many lines) but I boiled it down to this.

I've looked at several examples of how to use a GROUP BY condition, and... it seems like I am doing it right and it isn't working. Any help would be greatly appreciated, I've been working on this problem all day and can't get past it. Thanks!

 

[Fazza]

I don't know first().

Can you post an example of sample data, input & output?

Maybe instead, you can use MAX or MIN or TOP 1 ?

Normally, syntax is

SELECT function(some field), other field/s
FROM source
GROUP BY other field/s

 

[Danimoth]

I can't post results now, not at work. But, when I run it with any group by in there, it throws an error. I tried FIRST() and MIN(), and I even tried not putting in either and just doing the GROUP BY, and that threw an error too. The only thing I got to not throw an error was leaving out FIRST, MIN, and GROUP BY and just running the code without. It listed everything in those fields, some 500k items..

 

[xenou]

To my mind, you must use a SQL aggregate function in your SELECT clause, if there is to be a GROUP BY clause. SUM, MAX, MIN are aggregate functions, but FIRST is not. So at least with the original query you posted, it doesn't square right with me. Probably you'll need to give more specific examples, but generally as in Fazza's first post the syntax is:

SELECT Max(t.Field1) As MaxOfField1, t.Field2, t.Field3 
FROM Table1 t 
GROUP BY t.Field2, t.Field3;

(there is an aggregate function in the select clause, and the other fields are in the Group By clause).

 

[Danimoth]

Wow.

I changed first to MIN, without trying first again, and ran it and it did fine. I feel positive that I tried this yesterday and it didn't work, so either I am wrong about that or restarting the computer when I came in todayfixed it..

Thanks!

Dim CLin(1 To 1) As String
    CLin(1) = "SELECT TMRA, MIN(TSERIALNO) FROM fOWSerialNo GROUP BY TMRA"

 

[Danimoth]

Ok, now I try that in a join, and it throws an error.

    Dim CLin(1 To 3) As String
    CLin(1) = "SELECT n.tMRA, s.TSERIALNO, s.TMRA "
    CLin(2) = "FROM (SELECT TMRA, MIN(TSERIALNO) FROM fOWSerialNo GROUP BY TMRA) as s RIGHT OUTER JOIN fcompositeencm_text as n ON n.tMRA = s.TMRA "
    CLin(3) = "WHERE n.tMfgSiteCode = 'ESM'"

When I do it like this, however, it works, but it doesn't eliminate duplicate rows:

    Dim CLin(1 To 3) As String
    CLin(1) = "SELECT n.tMRA, s.TSERIALNO, s.TMRA "
    CLin(2) = "FROM (SELECT TMRA, (TSERIALNO) FROM fOWSerialNo) as s RIGHT OUTER JOIN fcompositeencm_text as n ON n.tMRA = s.TMRA "
    CLin(3) = "WHERE n.tMfgSiteCode = 'ESM'"

I can do it without the join like following and it works FINE, but as soon as I join it up, it throws an error. Or the join works fine without the aggregate and group by stuffs.

    Dim CLin(1 To 1) As String
    CLin(1) = "SELECT TMRA, MIN(TSERIALNO) FROM fOWSerialNo GROUP BY TMRA"

 

[Danimoth]

    Dim CLin(1 To 3) As String
    CLin(1) = "SELECT n.tMRA, s.SN, s.TMRA "
    CLin(2) = "FROM (SELECT TMRA, MIN(TSERIALNO) as SN FROM fOWSerialNo GROUP BY TMRA) as s RIGHT OUTER JOIN fcompositeencm_text as n ON n.tMRA = s.TMRA "
    CLin(3) = "WHERE n.tMfgSiteCode = 'ESM'"

This works. The "as SN" made it work, but I tried something very similar to this a minute ago and it didn't, I must have had some syntax wrong. Does it matter if I capitalize MIN, GROUP BY, AS, etc?

 

[xenou]

Normally Sql engines are case-insensitive and caps shouldn't matter, although in this case the As SN is required since you are using SN as the identifier in the Select clause.

 

[Danimoth]

When I did it without the As SN, I took the SN out of the select clause too, and put that back as TSERIALNO. I think I just have a really weird install of excel, but IT takes care of that.

Thanks for your help!