How to subtract based on condition in mysql query?

Asked Mar 16 '22 at 07:28

Active Mar 16 '22 at 09:23

Viewed 103 times

I am very new to the mysql part, i have one table which contains multiple columns from that i want to check two columns if column1 is greater than column2 it should subtract column1-column2(amount-amount_refunded) and it store as a amount which will not effect any current functionality can you please help me to acheive this thing..

what i am trying is

SELECT
IF((amount > amount_refunded),amount-amount_refunded ,IFNULL(sum(amount), 0)) AS amount,
//remaining query

error i got

SQL Error [1055] [42000]: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'loach.charges.amount' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

edited Mar 16 '22 at 09:23

asked Mar 16 '22 at 07:28

usersuser

What server are you using, is it local? – Ibrahim Abou Khalil Mar 16 '22 at 07:40
yes @IbrahimAbouKhalil, it is my local – usersuser Mar 16 '22 at 07:41
Ok and are you using phpmyadmin? – Ibrahim Abou Khalil Mar 16 '22 at 07:42
No i am using `Dbeaver-ce` tool – usersuser Mar 16 '22 at 07:43
Ok in your database you need to edit the variable 'sql_mode' and remove the value=only_full_group_by this might solve the issue. – Ibrahim Abou Khalil Mar 16 '22 at 07:47
Then same error will occur in prod server ryt..? – usersuser Mar 16 '22 at 07:52
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/242971/discussion-between-ibrahim-abou-khalil-and-usersuser). – Ibrahim Abou Khalil Mar 16 '22 at 07:53
1

@IbrahimAbouKhalil [take a look here](https://stackoverflow.com/questions/45484068/what-are-the-benefits-of-only-full-group-by-mode) = You don't have to turn it off if you use `GROUP BY` properly! – Simone Rossaini Mar 16 '22 at 07:59
@SimoneRossaini,before wrapping IF condition around the IFNULL it's not throwing any error – usersuser Mar 16 '22 at 08:05
2

[Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Raymond Nijland Mar 16 '22 at 09:02
Change `SUM (amount) ` to `amount` or group by the other columns of the select – Mar 16 '22 at 09:38

How to subtract based on condition in mysql query?

0 Answers0