问题:
I am trying to obtain multiple subtotals from an Access Table , using a external vbs file vbscript in Windows.
mySQL = "SELECT [Data.Time],"& Quantity &"*Sum([Da...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I am trying to obtain multiple subtotals from an Access Table , using a external vbs file vbscript in Windows.
mySQL = "SELECT [Data.Time],"& Quantity &"*Sum([Data.Price]) AS
SumOfPrice FROM Data WHERE ( ( [Data.Ticker] Like '"& CE1 &"'
Or [Data.Ticker] Like '"& CE2 &"' Or [Data.Ticker] Like '"& CE3 &"'
Or [Data.Ticker] Like '"& CE4 &"' Or [Data.Ticker] Like '"& CE5 &"'
Or [Data.Ticker] Like '"& PE1 &"' Or [Data.Ticker] Like '"& PE2 &"'
Or [Data.Ticker] Like '"& PE3 &"' Or [Data.Ticker] Like '"& PE4 &"'
Or [Data.Ticker] Like '"& PE5 &"'
) AND ([Data.DateTr]=#"& DateIn &"#))
GROUP BY [Data.Time] HAVING [Data.Time] > #"& startTime(i) &"# and (((Count([Data.Ticker]))= 10))"
Further Processing....
objRecordSet2.Open mySQL,objConnection, adOpenStatic, adLockOptimistic
objRecordSet2.MoveFirst
Do Until objRecordSet2.EOF
If objRecordSet2.Fields.Item(1) > testvalue
Presently it provides the total of CE+PE for each time (every 1 minute) Must ensure exactly 5 CE records and 5 PE records for every time instant , hence total 10.
Can we Modify it to provide totals of CE and PE seperately. SELECT Time , SumofCEPrice , SumofPEPrice for each 1 minute
回答1:
You could use conditional aggregation by changing your select
statement to:
"SELECT [Data.Time]," & Quantity & "*Sum(iif([Data.Ticker] Like '" & CE1 & "' or [Data.Ticker] Like '" & CE2 & "' or [Data.Ticker] Like '" & CE3 & "' or [Data.Ticker] Like '" & CE4 & "' or [Data.Ticker] Like '" & CE5 & "',[Data.Price],0)) AS SumofCEPrice "
With a SumofPEPrice
column built in a similar manner.
回答2:
Simply add, Ticker as another grouping variable in GROUP BY
. Additionally consider below items:
Any LIKE
expression without a wildcard operator, %
or *
, is redundant and should use the equality, =
. In fact, here you can use even an IN()
clause;
Non-aggregated columns should be left out of HAVING
and placed in WHERE
clause;
Use parameterization which is supported by DAO and ADO connections.
Adjusted SQL string
"SELECT [Data.Time], [Data.Ticker], "& Quantity &" * Sum([Data.Price]) AS SumOfPrice " & _
" FROM Data " & _
" WHERE ( " & _
" ( [Data.Ticker] = '"& CE1 &"' " & _
" OR [Data.Ticker] = '"& CE2 &"' OR [Data.Ticker] = '"& CE3 &"' " & _
" OR [Data.Ticker] = '"& CE4 &"' OR [Data.Ticker] = '"& CE5 &"' " & _
" OR [Data.Ticker] = '"& PE1 &"' OR [Data.Ticker] = '"& PE2 &"' " & _
" OR [Data.Ticker] = '"& PE3 &"' OR [Data.Ticker] = '"& PE4 &"' " & _
" OR [Data.Ticker] = '"& PE5 &"' " & _
" ) " & _
" AND ([Data.DateTr] = #" & DateIn & "#)" & _
" AND ([Data.Time] > #" & startTime(i) & "#) " & _
" ) " & _
" GROUP BY [Data.Time], [Data.Ticker] " & _
" HAVING Count(([Data.Ticker]) = 10)"
Alternatively, use parameterization with ADO Command:
' PREPARED STATEMENT (NO DATA)
mySQL = "SELECT [Data.Time], [Data.Ticker], ? * Sum([Data.Price]) AS SumOfPrice " & _
" FROM Data " & _
" WHERE ([Data.Ticker] IN (?, ?, ?, ?, ?, " & _
" ?, ?, ?, ?, ?)) " & _
" AND ([Data.DateTr] = ?) " & _
" AND ([Data.Time] > ?) " & _
" GROUP BY [Data.Time], [Data.Ticker] " & _
" HAVING Count(([Data.Ticker]) = 10)"
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = objConnection
.CommandText = mySQL
.CommandType = adCmdText
' BIND PARAMETERS CORRESPONDING TO ORDER OF ? PLACEMARKERS
.Parameters.Append .CreateParameter("pmQty", adInteger, adParamInput, , Quantity)
For Each var In Array(CE1, CE2, CE3, CE4, CE5, PE1, PE2, PE3, PE4, PE5)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, var)
Next var
.Parameters.Append .CreateParameter("pmDateIn", adDate, adParamInput, , DateIn)
.Parameters.Append .CreateParameter("pmStartTime", adDBTime, adParamInput, , startTime(i))
' BUILD RECORDSET FROM EXECUTION
Set objRecordSet2 = .Execute
End With
objRecordSet2.MoveFirst
Do Until objRecordSet2.EOF
If objRecordSet2.Fields.Item(1) > testvalue