Solved

# Sum with multiple conditions (self join?)

Posted on 2009-12-17
357 Views
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
``````
0
Question by:Phosphor

LVL 75

Accepted Solution

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

LVL 1

Author Comment

Thanks!
0

LVL 1

Author Closing Comment

Thanks, never summed with a case statement before.
0

## Featured Post

### Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.