February 7, 2013 at 10:54 am
I am trying to create a field in my query where I also SUM 'AMTPAYMCH' Where FieldD = 61 to get the discount amount for each row. Any help would be appreciated.
So my Discount Field will only have an amount where a field in my table (FieldD) is = 61
Thanks
SELECT IDCUST, IDINVC,SUM (AMTPAYMHC) AS Payments
FROM MyTable.dbo.AROBP
GROUP BY IDCUST, IDINVC
February 7, 2013 at 11:30 am
Hi
It would probably be best to post some sample data with an example of what you require, however I'll take a stab at what I think you are trying to achieve.
-- Some sample data
;with arobp (idcust, idinvc, amtpaymhc, fieldD) as (
select idcust, idinvc, amtpaymhc, fieldD
from (values
(1,1,100.0,0),
(1,1,51.2,0),
(1,1,5.8,61),
(1,2,51.4,61),
(1,2,546.0,0),
(2,3,21.0,0),
(2,3,546.7,0),
(2,3,13,61),
(2,3,15.47,0),
(2,3,15.4,61),
(3,4,7.4,0),
(3,4,87.8,0),
(3,4,91.1,0),
(3,4,54.7,0),
(3,4,864.8,0)) as a (idcust, idinvc, amtpaymhc, fieldD)
)
-- The query
SELECT IDCUST, IDINVC
,SUM (CASE WHEN FieldD <> 61 THEN AMTPAYMHC ELSE 0 END) AS Payments
,SUM (CASE WHEN FieldD = 61 THEN AMTPAYMHC ELSE 0 END) AS Discount
FROM AROBP
GROUP BY IDCUST, IDINVC
I have also removed the discount from the SUM for payments
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply