mysql 子查询group by_子查询在group by子句中返回多行 - MySQL

我'm trying to get my expected result of my query with the correct sum of amount per date, however, the Amount keeps adding up when it detected the same ID. I want to get the group by date. Unfortunately, when I ran my query it'收到错误说 Subquery returns more than 1 row

这是我的查询：

SELECT a.DOB as `DATE`, (CASE when f.INHOUR > 12 or f.INHour = 12 Then 'PM'

else 'AM' END) as Shift, concat(b.FIRSTNAME, ' ', b.LASTNAME) Fullname,

a.Amount as DECLARED, SUM(c.Amount) as CALCULATED,

a.AMOUNT as `NET OF SPECIAL SALES`,

d.Amount as `CASH OVER SHORT`,

a.AMOUNT as `CASH DEPOSIT`,

(SELECT SUM(gndrevn.AMOUNT) from gndrevn where gndrevn.ID= b.ID and

gndrevn.type = "4" group by gndrevn.DOB, gndrevn.ID) as `TRANSACTION COUNT (POS)`

FROM gndsale a

INNER JOIN emp b ON a.ID= b.ID

INNER JOIN gndsale c ON b.ID= c.ID

INNER JOIN gndsale d on b.ID = d.ID

INNER JOIN adjtime f on a.ID= f.ID

WHERE a.type = "22" AND c.type = "4" AND d.type = "42" and

STR_TO_DATE(a.DOB, '%m/%d/%Y') BETWEEN '2017-05-01' AND '2017-05-31' GROUP BY DECLARED

order by STR_TO_DATE(a.DOB, '%m/%d/%Y')

它返回此错误：

我试图删除 gndrevn.DOB, gndrevn.ID ，这是我的结果

列 Transaction Count POS 刚添加，因为它在表中检测到类似的ID .

我想要的Transaction Count POS的结果如下：

我使用不同的查询运行它，但它也需要在select语句中使用group by

这是查询：

SELECT ID as `EMPLOYEE`, sum(AMOUNT) as AMOUNT, DOB as DATE from gndrevn

where ID in (select ID from gndsale) and type = "4" group by

DOB, ID order by STR_TO_DATE(DOB, '%m/%d/%Y')

我认为我的group by语句有问题来获取查询 . 有人可以帮我吗？ :(