-1

I have the following table:

listId | accountId | amount 
1        1           20
1        1           20
2        2           30
2        2           30

I need to SUM(amount) and group by listId, accountId to get result:

 listId | accountId | amount | 
 1        1           40
 2        2           60

But it does not work for me: SUM(amount) ... GROUP BY listId, accountId

My full query is:

select `account_transactions`.*, 
`enterprise_invoces`.*, 
ABS(SUM(IF(AT_amount>0, AT_amount, 0))) AS debit, 
ABS(SUM(IF(AT_amount<0, AT_amount, 0))) AS credit 
from `account_transactions` 
inner join `enterprise_invoces` 
on `enterprise_invoces`.`AC_id` = `account_transactions`.`AT_code` 

where `AT_createuser` = 15 and 
date(`AT_transactiondatetime`) >= 2019-04-11 and
date(`AT_transactiondatetime`) <= 2019-07-29 and 
`AC_code` >= 601 and
`AC_code` <= 761 
group by `enterprise_invoces`.`AC_id`, `account_transactions.AT_transactionficheno` 
order by `AT_transactiondatetime` desc
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
POV
  • 11,293
  • 34
  • 107
  • 201
  • 1
    Post your complete query you have tryed.. Unless i mis something very obvious here the query `SELECT t.listId , t.accountId , SUM(t.ammount) FROM t GROUP BY t.listId , t.accountId` should give the expected results.. Which i assume you have tryed here? – Raymond Nijland Jul 06 '19 at 14:25
  • 1
    You need to provide the full query and the result you get. – Roman-Stop RU aggression in UA Jul 06 '19 at 14:25
  • Okay, I will post full query – POV Jul 06 '19 at 14:30
  • It does not work for me too, fails on `...` . – Serg Jul 06 '19 at 14:30
  • *"It does not work for me too, fails on ..."* i assume the `...` in the question is meant as BNF (Backus-Naur form) if that is the case you should not read it literal or use it like that.. @Serg – Raymond Nijland Jul 06 '19 at 14:34
  • I have added full query – POV Jul 06 '19 at 14:39
  • `account_transactions`.*, `enterprise_invoces`.* ipmlies all columns including aggregated. This definetly will fail. Explicitly list the cols needed. – Serg Jul 06 '19 at 14:44
  • I have read this answer, and got how does it work: https://stackoverflow.com/a/2421441/8291684 – POV Jul 06 '19 at 14:45
  • 3
    Your sample data and query have nothing to do with each other. – Gordon Linoff Jul 06 '19 at 14:49
  • Also that is generally also not how you should use group by.. see [manual](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) but MySQL 5.7.5+ supports a exception on the basic SQL 1992 rule which is called functionally dependent which might make your query valid.. – Raymond Nijland Jul 06 '19 at 14:55

1 Answers1

1

Your select query should not have other columns and should have only the columns mentioned in group by and also the column which needs to be aggregated. So the query should be like this below.

select enterprise_invoces.AC_id, account_transactions.AT_transactionficheno , ABS(SUM(IF(AT_amount>0, AT_amount, 0))) AS debit, ABS(SUM(IF(AT_amount<0, AT_amount, 0))) AS credit from account_transactions inner join enterprise_invoces on enterprise_invoces.AC_id = account_transactions.AT_code where AT_createuser = 15 and date(AT_transactiondatetime) >= 2019-04-11 and date(AT_transactiondatetime) <= 2019-07-29 and AC_code >= 601 and AC_code <= 761 group by enterprise_invoces.AC_id, account_transactions.AT_transactionficheno order by AT_transactiondatetime desc

mithz
  • 24
  • 4