CODEWITHSUNDEEP
mysqlsqlgroup-bywhere-clause
smalliest onefifty
smalliest onefifty

Reputation: 31

mysql group by error

below is a query I'm using

SELECT 
  `names`, sum(cashin.amount) as amountin, 
  sum(cashout.amount) as amountout, 
  (sum(cashin.amount) - sum(cashout.amount)) as total 
FROM (`client`) 
INNER JOIN `cashin` ON `cashin`.`clientid`=`client`.`id` 
INNER JOIN `cashout` ON `cashout`.`clientid`=`client`.`id` 
WHERE (sum(cashin.amount) - sum(cashout.amount)) < 0 
GROUP BY `client`.`id`

Problem is I get an error:

Invalid use of group function

Replacing the function in where clause with field alias 'total' I still get an error:

Unknown column total

How can this query be fixed?

Upvotes: 2

Views: 881

Answers (4)

echo_Me
echo_Me

Reputation: 37233

you make an alias of this

   (sum(cashin.amount) - sum(cashout.amount)) as total

as total so why u dont use it here ?

  WHERE (sum(cashin.amount) - sum(cashout.amount)) < 0

replace it by

     WHERE total < 0

and this 

 FROM (`client`)

just use

  FROM `client`

and this

  (sum(cashin.amount) - sum(cashout.amount)) as total  // line 4

replace it by

   (amountin - amountout) as total

and it will work good for you.

Upvotes: 0

Taryn
Taryn

Reputation: 247720

As an alternative to using a HAVING clause, you can wrap your query in a SELECT statement and then place the filter in a WHERE clause:

select names,
  amountin,
  amountout,
  total
from
(
  SELECT 
    `names`, 
    sum(cashin.amount) as amountin, 
    sum(cashout.amount) as amountout, 
    (sum(cashin.amount) - sum(cashout.amount)) as total 
  FROM client
  INNER JOIN `cashin` ON `cashin`.`clientid`=`client`.`id` 
  INNER JOIN `cashout` ON `cashout`.`clientid`=`client`.`id` 
  GROUP BY names
) src
where total < 0

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

SELECT 
  `names`, 
  sum(cashin.amount) as amountin, 
  sum(cashout.amount) as amountout, 
  (sum(cashin.amount) - sum(cashout.amount)) as total 
FROM client
INNER JOIN `cashin` ON `cashin`.`clientid`=`client`.`id` 
INNER JOIN `cashout` ON `cashout`.`clientid`=`client`.`id` 
GROUP BY names
HAVING total < 0

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79949

Use HAVING instead of WHERE, and put the GROUP BY names clause instead of GROUP BY id before the HAVING clause like so:

SELECT 
  `names`, 
  sum(cashin.amount) as amountin, 
  sum(cashout.amount) as amountout, 
  (sum(cashin.amount) - sum(cashout.amount)) as total 
FROM client
INNER JOIN `cashin` ON `cashin`.`clientid`=`client`.`id` 
INNER JOIN `cashout` ON `cashout`.`clientid`=`client`.`id` 
GROUP BY names
HAVING (sum(cashin.amount) - sum(cashout.amount)) < 0

Upvotes: 1

Related Questions