MySQL GROUP_CONCAT() and SUM()

user_18860677 0
Viewed 33

For example, I have the following table:

id     type         credit
1      loan1        300
2      loan1        200
3      loan2        500  
4      loan2        300
5      allowance1   400
6      allowance2   300

I want to display them in a single row like this

id          loan
1    loan1: 500, loan2: 800

so far, I have the following query which displays them in a single row

GROUP_CONCAT(CASE WHEN type LIKE 'loan%' THEN concat(type, ': ', credit) ELSE NULL END SEPARATOR ', ') as loan

output:

id                 loan
1    loan1: 300, loan1: 200, loan2: 500, loan2: 300

How do I display only distinct types and sum their credit?

mysql-workbench
1 Answers

A few minutes of googling would have provided this article

SELECT id, type, SUM(credit)
FROM table
GROUP BY type;

Group by will group all equal types together. If you just want to get the distinct type use SELECT DISTINCT type from table

user_108447180
Related