SQL query to return "total" row?

[Jaymond Flurrie]

I have a working code which I thought could be optimized quite a bit. The idea is that I have a table (actually Excel sheet) with 100 000 records and 30 columns. I would need to pull out an SQL query which returns one record with 30 columns and this would include the sum of each 100 000 record -column. The easy part here is that each of the 3 million cells are simply just a zero or one so basically it is just a bitmap.

So far I've done this with 30 separate queries, but tell me it doesn't have to be so slow. What's the way to do this right?

 

[xenou]

SELECT SUM(Field1), Sum(Field2), Sum(Field3), ... , Sum(Field30) FROM Table1;

Seems simple enough. Is there more to this problem? Access does a pitiful job with field names as you can't write:
Sum(Field1) As Field1
And by default get:
Sum(Field1) As SumOfField1

But if the query goes into a form you can alter the column labels.

ξ

 

[Jaymond Flurrie]

SELECT SUM(Field1), Sum(Field2), Sum(Field3), ... , Sum(Field30) FROM Table1;

Seems simple enough. Is there more to this problem? Access does a pitiful job with field names as you can't write:
Sum(Field1) As Field1
And by default get:
Sum(Field1) As SumOfField1

But if the query goes into a form you can alter the column labels.

ξ

Oh ok, I tried something like Select SUM (*) and that didn't work. But without trying I'd say your code is exactly what I was looking for. Thanks!

 

[Jaymond Flurrie]

Now I got to try it, here's the code without result handling:

Sub ItemSums()
    'Count sums for item groups
    Dim strProv As String 'Data provider for SQL
    Dim strDataSrc As String 'Data source for SQL ie. this book
    Dim strXPro As String 'Extended properties for SQL
    Dim strConnect As String 'Connection string for SQL
    Dim strSelect As String 'Select-part of SQL query
    Dim strSrcTable As String 'SourceTable
    Dim strSQL As String 'Full SQL query
    Dim rsData As ADODB.Recordset 'Recordset that ADODB returns
    
    Dim L As Long, k As Long 'Loop-variables
    Dim wkbOA As Workbook 'Source workbook
    
    Dim vTR As Variant 'Item group names
    Dim vResults As Variant 'Results
    
    
    'Define source
    ThisWorkbook.Activate
    gstrOstoAineisto = Range("Ostoaineisto")
    strProv = "Provider=Microsoft.ace.oledb.12.0;"
    strDataSrc = "Data Source=" & gstrOstoAineisto & ";"
    strSrcTable = " FROM [Ostoaineisto$];"
    strXPro = "extended properties=excel 12.0"
    
    Application.StatusBar = "Counting item groups sizes..."
    
    'Create the connection string
    strConnect = strProv & strDataSrc & strXPro
    
    
    'Get item group names
    With ThisWorkbook.Sheets("SolverIn")
        'Only one group?
        If .Range("C7") = vbNullString Then
            Range("B7").Name = "GroupNames"
        Else
            Range(.Range("B7"), .Range("B7").End(xlToRight)).Name = "GroupNames"
        End If
        ReDim vTR(1, 1 To .Range("GroupNames").Count)
        vTR = .Range("GroupNames")
    End With
    
    
    Set wkbOA = Workbooks.Open(gstrOstoAineisto)
    
    strSelect = vbNullString
    
    'Rename fields so that numbers as names won't blow up the whole code
	'Also parse the SELECT-part of SQL-query
    With wkbOA.Sheets(1).Range("A1")
        For L = LBound(vTR, 2) To UBound(vTR, 2)
            .Offset(0, L) = "a" & .Offset(0, L) & "a"
            strSelect = strSelect & "SUM(" & "a" & vTR(1, L) & "a" & "), "
        Next L
    End With
    
    ThisWorkbook.Activate
    
	'Remove the last ", " and add in FROM-part
    strSelect = "SELECT " & Left(strSelect, Len(strSelect) - 2)
    strSQL = strSelect & strSrcTable
    
	'Open connection
    Set rsData = New ADODB.Recordset
    rsData.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    'Check to make sure we received data
    If Not rsData.EOF Then
        vResults = rsData.GetRows(rsData.RecordCount)
    End If
 	
    'Clean up our recordset object.
    rsData.Close
    Set rsData = Nothing
        
    With wkbOA.Sheets(1).Range("A1")
        'Re-rename the fields
        For L = LBound(vTR, 2) To UBound(vTR, 2)
            .Offset(0, L) = Mid(.Offset(0, L), 2, Len(.Offset(0, L)) - 2)
        Next L
    End With
    
    wkbOA.Close SaveChanges:=False
End Sub

That strsql for the query is:
SELECT SUM(a10a), SUM(a11a), SUM(a12a), SUM(a13a), SUM(a14a), SUM(a15a), SUM(a16a), SUM(a17a), SUM(a19a), SUM(a20a), SUM(a21a), SUM(a22a), SUM(a23a), SUM(a24a), SUM(a25a), SUM(a26a), SUM(a30a), SUM(a31a), SUM(a32a), SUM(a33a), SUM(a34a), SUM(a35a), SUM(a36a), SUM(a37a), SUM(a38a), SUM(a39a), SUM(a40a), SUM(a41a), SUM(a42a), SUM(a43a), SUM(a44a), SUM(a45a), SUM(a48a), SUM(a49a), SUM(a50a), SUM(a53a), SUM(a54a), SUM(a59a), SUM(a60a), SUM(a61a), SUM(a62a), SUM(a63a), SUM(a64a), SUM(a65a), SUM(a66a), SUM(a67a), SUM(a68a), SUM(a69a), SUM(a70a), SUM(a71a), SUM(a72a), SUM(a73a), SUM(a74a), SUM(a75a), SUM(a76a), SUM(a77a), SUM(a78a), SUM(a79a), SUM(a80a), SUM(a82a), SUM(a83a), SUM(a84a), SUM(a85a), SUM(a86a), SUM(a87a), SUM(a88a), SUM(a89a) FROM [Ostoaineisto$];


The result is that I get an error '-2147217904': "No value given for one or more required parameters". Any ideas what causes this?

edit. And no, it's not this gstrOstoAineisto, that is set on global level to a filepath and it worked with that my original version where I made 30 separate queries.

 

[Jaymond Flurrie]

It works now. The problem was having incorrect column names, but that's fixed now. So yes, nothing wrong with your suggestion, does exactly what I wanted, thanks!