CODEWITHSUNDEEP
mysqlconcatenationgroup-concat
Dipak
Dipak

Reputation: 939

GROUP_CONCAT within CONCAT

Database hierarchical structure is as follow

  • Student Name
    • List of fee Assigned to Each Student
      • List of Scholarship Assigned to Each Fee

As structure, expected output is

Student Name-Fee->Scholarship1, Scholarship2

Karan-1.Annual Fee->Economic Scholarship,Incapable Scholarship,2.Monthly Fee

But what I am getting

Student Name-Fee->Scholarship1, Student Name-Fee->Scholarship2

Karan-1.Annual Fee->Economic Scholarship,1.Annual Fee->Incapable Scholarship,2.Monthly Fee

What is wrong in here ? Though I am nesting CONCAT, but not getting expected output

CONCAT(student.en_ttl,'-',GROUP_CONCAT(DISTINCT fee.id,'.',fee.en_ttl,
    COALESCE(CONCAT('->',sch.en_ttl),''))) AS fee

SQL Fiddle

Upvotes: 0

Views: 55

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You basically need to two levels of GROUP BY. So, we will need to use a Derived Table here. First subquery will aggregate at the level of fee; and then the second level would aggregate those fee details at the level of student.

Also, in newer (and ANSI SQL compliant) versions of MySQL, you need to ensure that any non-aggregated column in the SELECT clause should be in the GROUP BY clause as well.

Query

SELECT
  CONCAT(stud_ttl,'-',GROUP_CONCAT(CONCAT(fee_det, COALESCE(CONCAT('->',fee_sch), '')))) AS fee
FROM 
(
  SELECT  student.ttl AS stud_ttl, 
        CONCAT(fee.id,'.',fee.ttl) AS fee_det,   
        Group_concat(DISTINCT sch.ttl) AS fee_sch 
  FROM   inv_id
       JOIN student
         ON student.id = inv_id.std
       JOIN inv_lst
         ON inv_lst.ftm = inv_id.ftm
       JOIN fee
         ON fee.id = inv_lst.fee
       JOIN sec_fee
         ON sec_fee.fee = fee.id
            AND sec_fee.cls = student.cls
            AND sec_fee.sec = student.sec
       LEFT JOIN std_sch
              ON std_sch.std = student.id
       LEFT JOIN sec_sch
              ON sec_sch.sch = std_sch.sch
                 AND sec_sch.fee = fee.id
       LEFT JOIN sch
              ON sch.id = sec_sch.sch
  GROUP  BY student.ttl, fee_det, fee.ttl
) dt
GROUP BY stud_ttl;

Result

| fee                                                                  |
| -------------------------------------------------------------------- |
| Karan-1.Annual->Economic Scholarship,Incapable Scholarship,2.Monthly |

View on DB Fiddle

Upvotes: 1

Related Questions