dongyan8896
dongyan8896
2019-02-08 16:11

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

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dqdmvg7332 dqdmvg7332 2年前

    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);
    
    点赞 评论 复制链接分享
  • dpxyfa4718 dpxyfa4718 2年前

    Your question needs more clarification but I assumed you wanted to compare type 1 amount per year,created at, type to the total yearly amount.

    select year(created_at), created_at, type,
           sum(case when type =1 then amount else 0 end) as type1amount,
           sum(amount) over (partition by year(created_at)) as yeartotal
    from table
    group by 1,2,3
    
    点赞 评论 复制链接分享
  • dtx9931 dtx9931 2年前

    You just need one simple query for this. Take the year of the date, sum the amount and group by year and type. FYI, you have type as a field twice in your question. That's not possible, so I'm assuming that it's really some other field name the second time.

    SELECT
        YEAR(created_at) AS year, 
        type,
        SUM(amount) AS amount
    FROM mytable 
    GROUP BY YEAR(created_at), type
    ORDER BY YEAR(created_at)
    

    DEMO

    点赞 评论 复制链接分享

为你推荐