SQL查询从同一个字段中获取2个和

Question

I am trying to have the total "amount" with "type" = 1 and with type = 2 by year of the "created_at" field.

Here is the table

+-----------------+------------------+------+
| Field           | Type             | Null | 
+-----------------+------------------+------+-----
 | id              | int(10) unsigned | NO   | 

 | type            | int(10) unsigned | YES  |

 | type            | varchar(255)     | NO   | 
| amount          | double(11,2)     | NO   |                           
| created_at      | timestamp        | YES  |
| updated_at      | timestamp        | YES  |   

Here is my code (It only gives the total for type = 1):

DB::select(
        'SELECT 
                year, 
                created_at,
                type,
                SUM(amount) AS total
          FROM 
              (SELECT 
                YEAR(created_at) AS year, 
                amount, 
                created_at, 
                type 
              FROM table 
              WHERE type= 1) AS t 
          GROUP BY year, type
          ORDER BY year;
    ');

thanks in advance for any help.

Answer 1

I suspect you really want conditional aggregation:

SELECT YEAR(created_at) AS year, 
       SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) as amount_1,
       SUM(CASE WHEN type = 2 THEN amount ELSE 0 END) as amount_2
FROM table 
GROUP BY YEAR(created_at);