我'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语句有问题来获取查询 . 有人可以帮我吗？ :(
1 回答
我想你想要的是从GROUP BY中删除gndrevn.DOB并在WHERE中添加gndrevn.DOB = a.DOB .