write query to get expected output using mysql

Question

table name: transactions

customer	debit	credit
a	70	50
a	100	20
a	20	60
b	100	20
b	40	80
b	10	30
c	100	200
c	100	30
c	80	90
d	100	200
d	90	30
d	80	90
e	100	100
e	100	30
e	80	90

check who have positive or negative bal_type; if(total of debit-total of credit>0 ) positive else negative

I try below:

SELECT  CASE WHEN (SUM(debit)-SUM(credit))<0 THEN "negative" 
 ELSE "positive"
 END AS bal_type,
 customer
FROM 
    transactions
GROUP BY customer

MY OUTPUT:

bal_type	customer
positive	a
positive	b
negative	c
negative	d
positive	e

expecting:

bal_type	customer
positive	e,a,b
negative	c,d

NOTE: bal_type is derived type column. positive customer name should be order by DESC using there SUM(debit)-SUM(credit). and negative customer name should be order by ASC using there SUM(debit)-SUM(credit).

Is it really intended to select "negative" for zero or is your logic incomplete? — Jonas Metzler
– Jonas Metzler, Commented Jan 15, 2023 at 12:33

forpas · Accepted Answer · 2023-01-15 10:55:29Z

3

You need 2 levels of aggregation and GROUP_CONCAT() aggregate function to concatenate the customers:

SELECT bal_type,
       GROUP_CONCAT(customer ORDER BY customer) AS customer
FROM (
  SELECT CASE WHEN SUM(debit)- SUM(credit) > 0 THEN 'positive' ELSE 'negative' END AS bal_type,
         customer
  FROM transactions
  GROUP BY customer
) AS t
GROUP BY bal_type;

See the demo.

answered Jan 15, 2023 at 10:55

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prasad Sanap Over a year ago

Please check updates into question. I need query according to these changes.

Collectives™ on Stack Overflow

write query to get expected output using mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

customer	debit	credit
a	70	50
a	100	20
a	20	60
b	100	20
b	40	80
b	10	30
c	100	200
c	100	30
c	80	90
d	100	200
d	90	30
d	80	90
e	100	100
e	100	30
e	80	90

customer	debit	credit
a	70	50
a	100	20
a	20	60
b	100	20
b	40	80
b	10	30
c	100	200
c	100	30
c	80	90
d	100	200
d	90	30
d	80	90
e	100	100
e	100	30
e	80	90

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

customer	debit	credit
a	70	50
a	100	20
a	20	60
b	100	20
b	40	80
b	10	30
c	100	200
c	100	30
c	80	90
d	100	200
d	90	30
d	80	90
e	100	100
e	100	30
e	80	90