现数据库中有字段
workid,username,mianji,ttime,wage
我想的是查找所有username相同的数据,让后求出ttime的总和,但是显示的时候是username,workid只显示一条,mianji,ttime显示相同username的和。我写的语句是select workid,username,sum(mianji),sum(ttime),wage from wage group by workid,username
所有数据除了usename都是数字格式,workid和username是一一对应的。
这条语句查询不出来,提示“试图执行的查询里不包含作为聚合函数的特定表达式”。请问应该怎么写才能得到我要的效果。
workid,username,mianji,ttime,wage
我想的是查找所有username相同的数据,让后求出ttime的总和,但是显示的时候是username,workid只显示一条,mianji,ttime显示相同username的和。我写的语句是select workid,username,sum(mianji),sum(ttime),wage from wage group by workid,username
所有数据除了usename都是数字格式,workid和username是一一对应的。
这条语句查询不出来,提示“试图执行的查询里不包含作为聚合函数的特定表达式”。请问应该怎么写才能得到我要的效果。
(Select workid,sum(mianji) as mj,sum(ttime) as tt from wage group by workid) t2
where t1.workid=t2.workid
Private Sub Form_Load()
Set conn = New Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & "c:\bwscale.mdb;Persist Security Info=False"
Dim rs As New ADODB.Recordset
SQL = "select sum(ttime) as aaa from bwmain where username=" & Chr(39) & "张三" & Chr(39)
rs.Open SQL, conn, adOpenStatic, adLockOptimistic
TotalVal = rs("aaa")
rs.Close
'****************统计后是只有一笔,故关掉重开,反正总和已取到了
SQL = "select * from bwmain"
rs.Open SQL, conn, adOpenStatic, adLockOptimistic
MsgBox TotalVal
End Sub