SQL SUM of distinct rows.... I'm stuck!

Question

Hello,

Been trying to put together an SQL query that sorts data into financial years (which I've sorted) and then SUMs it according to each distinct value in the claim_id column

table structure is: claim(id, claim_id, date, amount)

SQL so far is:

 SELECT
    CASE 
       WHEN MONTH(date)>=4 THEN
            concat(YEAR(date), '-',YEAR(date)+1)
       ELSE 
            concat(YEAR(date)-1,'-', YEAR(date)) 
    END AS fyear,
    SUM(amount) AS total, claim_id
 FROM claim  
 GROUP BY fyear, claim_id 
 ORDER BY claim_id ASC, date ASC

At the moment I'm getting this

Array
(
    [fyear] => 2009-2010
    [total] => 567.89
    [claim_id] => 1
)

Which is really close, except of course because of the GROUP BY claim_id I'm getting another array for each distinct claim_id:

Array
(
    [fyear] => 2009-2010
    [total] => 106.76
    [claim_id] => 2
)

What I'd actually like is:

Array
(
    [fyear] => 2009-2010
    [total1] => 567.89
    [total2] => 106.76
)

Any suggestions please?

Thanks!

Answer 1

Would something like...

SELECT * FROM
    SELECT (CASE 
                WHEN MONTH(date)>=4 THEN
                     concat(YEAR(date), '-',YEAR(date)+1)
                ELSE 
                     concat(YEAR(date)-1,'-', YEAR(date)) 
                END AS fyear,
                SUM(amount) AS total, claim_id
        FROM claim
        WHERE claim_id = 1
        GROUP BY fyear
        ORDER BY date ASC),
    SELECT (CASE 
                WHEN MONTH(date)>=4 THEN
                     concat(YEAR(date), '-',YEAR(date)+1)
                ELSE 
                     concat(YEAR(date)-1,'-', YEAR(date)) 
                END AS fyear,
                SUM(amount) AS total, claim_id)
        FROM claim
        WHERE claim_id = 2
        GROUP BY fyear
        ORDER BY date ASC)
    FROM dual;

be of any use?

Answer 2

Wrapping your existing query in another SELECT and using GROUP_CONCAT might do what you are after. I've tested the SQL itself, but am not sure if the results are what you want - all your totals will appear in one column as a comma separated list:

SELECT fyear, GROUP_CONCAT(total) FROM (
    SELECT
        CASE 
           WHEN MONTH(date)>=4 THEN
                concat(YEAR(date), '-',YEAR(date)+1)
           ELSE 
                concat(YEAR(date)-1,'-', YEAR(date)) 
        END AS fyear,
        SUM(amount) AS total, claim_id
    FROM table1  
    GROUP BY fyear, claim_id 
    ORDER BY claim_id ASC, date ASC
) AS totals GROUP BY fyear;