• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1274
  • Last Modified:

how to sum the amount based on conditions ?

I have a view called vw_invoice which has attributes amount, fiscal_key, splr_name, splr_id, adv_flag.

I need to sum all the amounts for current fiscal year, sum all the amounts for all the fiscal periods thus far -  by supplier,sum all the amounts
thus far for supplier where adv_flag is true, sum all the amounts for the current fiscal period where adv_flag is true.

eg: the output would be

splr_id , splr_name, address, amt_current_fiscal, sum_total, amt_current_fiscal_adv_flag, sum_total_adv_flag

1   tesname, houston, 100000.00, 20000000.00, 500000.00, 5430000.00

select splr_id, splr_name, sum(amount) as sum_total from vw_invoice
group by splr_id, splr_name --- this would get me current total amount how would I get the rest ?

the fiscal_key would be a parameter.
1 Solution
SELECT   splr_id, splr_name, address,
                 WHEN fiscal_key = :current_period
                     THEN amount
                 ELSE NULL
             END) amt_current_fiscal, SUM(amount) sum_total,
                 WHEN fiscal_key = :current_period AND adv_flag = 'TRUE'
                     THEN amount
                 ELSE NULL
            ) amt_current_fiscal_adv_flag,
                 WHEN adv_flag = 'TRUE'
                     THEN amount
                 ELSE NULL
             END) sum_total_adv_flag
    FROM vw_invoice
GROUP BY splr_id, splr_name, address
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now