总和后选择的情况？ [英] Case in select after sum?

问题描述

我正在尝试根据日期将不同的列分配给总和。
这是我所拥有的：

I'm trying to assign a different column to a sum depending on the dates. Here's what I have:

SELECT SUM(amount) CASE WHEN date BETWEEN '1/1/13' AND '1/1/14' THEN Last_yr_tot 
        WHEN date BETWEEN '1/1/14' AND 'now' THEN YTD

我的整个查询。

select  fd.location_cd
    ,round(sum(case when fd.fs_dt between '1/1/13' and '12/31/13' then amount else 0 end)::numeric, 0) as Last_yr
    ,round(sum(case when fd.fs_dt between '1/1/14' and current_date then amount else 0 end)::numeric, 0) as YTD
    ,round(sum(case when date_part('month', fd.fs_dt) = 1 then amount end)::numeric, 0) January_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 2 then amount end)::numeric, 0) February_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 3 then amount end)::numeric, 0) March_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 4 then amount end)::numeric, 0) April_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 5 then amount end)::numeric, 0) May_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 6 then amount end)::numeric, 0) June_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 7 then amount end)::numeric, 0) July_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 8 then amount end)::numeric, 0) August_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 9 then amount end)::numeric, 0) September_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 10 then amount end)::numeric, 0) October_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 11 then amount end)::numeric, 0) November_data
    ,round(sum(case when date_part('month', fd.fs_dt) = 12 then amount end)::numeric, 0) December_data

from financial_acct fa
    join financial_data fd
    on fd.acct_no = fa.acct_no

inner join (select sum(fd.amount)
    from financial_data fd
    where fd.fs_dt between '1/1/13' and '1/1/14'
    ) Last_yr
    on fa.acct_no = fd.acct_no

where fa.acct_no = '799-' and fd.fs_dt between '1/1/14' and 'now' 
group by fd.location_cd
    ,fd.fs_dt
order by fd.location_cd

有人要求我将整个查询都放入帖子中。我会遵守的，但需要更多细节。 acct_no是帐号。 fs_dt是日期。 location_cd是代码的去向。

It was requested that I put my entire query into the post. I will comply, but it needs more details. acct_no is account number. fs_dt is the date. location_cd is where the code is going.

推荐答案

您需要将它们分成两列。每个查看日期范围并返回金额值或零

You need to split these out into two columns. Each looking at the date range and returning the amount value or zero

select
  sum(case when datecolumn between '1/1/13' and '1/1/14' then amount else 0 end) as last_yr_tot,
  sum(case when datecolumn between '1/1/14' and current_date then amount else 0 end) as ytd
from
  myTable

检查此小提琴

请注意， datecolumn 和 myTable 必须替换为您自己的日期列和表名。由于 date 是postgresql中的保留字，因此它看起来不太像列名。

Note that datecolumn and myTable must be replaced by your own date column and table names. As date is a reserved word in postgresql, it doesnt look much like a column name.

这篇关于总和后选择的情况？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！