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

Sum with multiple conditions (self join?)

Hello experts.
How can I get sums for multiple items conditionally from this dataset?
The values in code window come from this query:
SELECT     pro_rec, pro_acct, pro_dep, pro_charge, pro_date, pro_pri_clm_, pro_type_,
pro_pmt_meth_, pro_unique_
FROM         prohist
WHERE     (pro_rec = 27757)

I need to Sum the following by pro_date... (Note this set only uses one date)
1.) All where pro_pri_clm = 0
2.) All where pro_type_ = 0 AND pro_pmt_meth_ = 0
3.) All Where pro_type_ = 2 AND pro_pmt_meth_ >2 (this set uses 3 but there are others)
4.)  All Where pro_type_ = 1 AND pro_pmt_meth_ = 1 AND pro_dep <>0

In this return format:
pro_account | SUM1 | SUM2 | SUM3 | SUM4
 27757          | Value | Value  | Value | Value

pro_rec  pro_acct pro_dep  pro_charge pro_date pro_pri_clm pro_type pro_pmt_meth pro_unque
27757	150	2	99243	2009-09-10	1	0	0	1
27757	85	5	73130	2009-09-10	1	0	0	1
27757	20	1	99243	2009-09-10	0	1	3	4
27757	-20	1	99243	2009-09-10	0	1	0	5
27757	128.72	1	99243	2009-09-10	1	1	1	6
27757	21.07	1	99243	2009-09-10	1	2	3	7
27757	128.72	0	73130	2009-09-10	1	1	1	6
27757	53.03	1	73130	2009-09-10	1	2	3	8
27757	5.79	1	99243	2009-09-10	15	1	1	9
27757	20	1	99243	2009-09-10	0	1	0	12

Open in new window

0
Phosphor
Asked:
Phosphor
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
select pro_rec, sum(case when  pro_pri_clm = 0  then pro_charge  else 0 end) as sum1,
 sum(case when pro_type_ = 0 AND pro_pmt_meth_ = 0  then  then pro_charge  else 0 end) as sum2,
 sum(case when  pro_type_ = 2 AND pro_pmt_meth_ >2   then pro_charge  else 0 end) as sum3,
 sum(case when pro_type_ = 1 AND pro_pmt_meth_ = 1 AND pro_dep <>0  then pro_charge  else 0 end) as sum4
FROM         prohist
group by datecolumn
0
 
PhosphorAuthor Commented:
Thanks!
0
 
PhosphorAuthor Commented:
Thanks, never summed with a case statement before.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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