CODEWITHSUNDEEP
mysqlsqlselectjoingroup-by
Virgil Cruz
Virgil Cruz

Reputation: 191

Subquery returns more than 1 row in group by clause - MySQL

I'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's getting an error saying Subquery returns more than 1 row

Here's my query:

 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')

It returns this error:

enter image description here

I tried to remove gndrevn.DOB, gndrevn.ID and this is my result

enter image description here

The Column Transaction Count POS just added because it detected similar ID's in the table.

What the result I want for my Transaction Count POS is this:

enter image description here

I ran this with a different query, but it also requires a group by in a select statement

Here's the query:

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')

I think I have a problem with my group by statement to get the query. Anyone can help me on this? :(

Upvotes: 0

Views: 985

Answers (1)

Anton
Anton

Reputation: 26

I think what you want is to remove the gndrevn.DOB from the GROUP BY and add gndrevn.DOB = a.DOB in the WHERE.

Upvotes: 1

Related Questions