Group by SQL query in VBA

Question

I have a query where I would like to consolidate by a Code that has two rows in the initial tab, however I keep getting an error Cannot group on Fields selected with '*'. Any ideas

Option Explicit

Sub get_code()    
   OptimizeVBA True: ShDel ("Workings")    
   Dim cn As ADODB.Connection
   Set cn = New ADODB.Connection

   With cn
       .Provider = "Microsoft.ACE.OLEDB.12.0"
       .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Macro;IMEX=1;HDR=YES"";"
       .Open
   End With

   Dim rs As ADODB.Recordset
   Set rs = New ADODB.Recordset

   rs.Open "SELECT * FROM [Holdings$] WHERE [Holdings$].['Code$'] ='%91' GROUP BY ['Code']", cn

   Dim fld As ADODB.Field
   Dim i As Integer

   Sheets.Add.name = "Workings"
   With ThisWorkbook.Worksheets("Workings")
      i = 0
      For Each fld In rs.Fields
         i = i + 1
         .Cells(1, i).Value = fld.name
       Next fld
       .Cells(2, 1).CopyFromRecordset rs
       .UsedRange.Columns.AutoFit
   End With

   rs.Close
   cn.Close
   OptimizeVBA False
End Sub

Optimize just optimizes the workbook and SHdel deletes the sheet.

General GROUP BY tip: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." — jarlh, Jul 05 '17 at 14:33
Perhaps you mean `ORDER BY ['Code']`? grouping by implies aggregation (min/max/avg/count) which you're doing none. or distinct to eliminate duplicates? — xQbert, Jul 05 '17 at 14:40
And why is the field quoted? Is there really quotes in that column header. — Parfait, Jul 05 '17 at 14:44
About @xQbert's comment: https://stackoverflow.com/a/164544/1188513 — Mathieu Guindon, Jul 05 '17 at 14:48
@xQbert I wanted to sum by a var, but I have 50 column headers, maybe a subtotal is easier — Lowpar, Jul 05 '17 at 14:52
what SQL engine is this? would an windowfunction/analytic work to get the "sum" w/o any group by? Such as `sum(var) over (partition by unique key/columns)` — xQbert, Jul 05 '17 at 14:53

Mathieu Guindon · Answer 1 · 2017-07-05T15:04:48.040

0

Cannot group on Fields selected with '*'

Pretty explicit: you can't GROUP BY when you do SELECT *. Specify the field list explicitly.

SELECT [Code$], [Foobar] FROM [Holdings$] WHERE [Holdings$].['Code$'] ='%91' GROUP BY [Code$], [Foobar]

Note that all fields in the SELECT clause that aren't aggregates, must appear in the GROUP BY clause.

If there is no need to aggregate anything, you can remove the GROUP BY clause and do SELECT DISTINCT instead.

edited Jul 05 '17 at 15:04

answered Jul 05 '17 at 14:38

Mathieu Guindon

69,817
8
107
235

I see, makes sense when you put it like that – Lowpar Jul 05 '17 at 14:40
Why GROUP BY when no aggregate functions are involved, instead of a simple SELECT DISTINCT? – jarlh Jul 05 '17 at 14:49
@jarlh [because it makes absolutely no difference whatsoever](https://stackoverflow.com/a/164544/1188513) and that's what the OP had. – Mathieu Guindon Jul 05 '17 at 14:50
@Mat's mug for SQL server, oracle that's true. for mySQL it's not. and even in SQL server and oracle, more complex queries may result in different execution plans where the group by and distinct use result in different plans. (we don't know the engine here though it's implied it's a MSFT product) – xQbert Jul 05 '17 at 14:55
Well, that's a matter of taste. OP has 50 columns. – jarlh Jul 05 '17 at 14:55
@xQbert my bad, per the connection string it would seem the source is an Excel worksheet, so the "engine" is the `Microsoft.ACE.OLEDB.12.0` provider. – Mathieu Guindon Jul 05 '17 at 14:58
@jarlh OP is asking why they can't `GROUP BY` with `SELECT *`. Answering "use distinct instead" doesn't help OP understand. – Mathieu Guindon Jul 05 '17 at 14:59

Group by SQL query in VBA

1 Answers1