Sum statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum statement

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-06-27 : 08:42:43

I have a table ACCOUNTDETAIL that has

POLICY_NUMBER ACTIVITY_GROUP
CP 10004 A NULL
CP 10004 R 7558.00
CP 10004 B 3148.81
CP 10004 A NULL
CP 10004 A NULL
CP 10004 R 7558.00
CP 10004 B 8187.58
CP 10004 R -7558.00
CP 10004 B 7558.00
CP 10004 P -1259.66
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.83
CP 10004 P -629.86
CP 10004 B 7558.00
CP 10004 A .00
CP 10004 P 629.00
CP 10004 A .82

I am trying to display three columns

POLICY_NUMBER TOTAL_B TOTAL_P
CP 10004 26452.29 7558.82

I have written this script but it is not working

SELECT A.POLICY_NUMBER, A.ACCOUNT_SET,SUM(A.DETAIL_AMOUNT) WHERE ACTIVITY_GROUP = 'P' AS TOTAL_AMOUNT_PAYMENT),(SUM(A.DETAIL_AMOUNT) WHERE ACTIVITY_GROUP = 'B' AS TOTAL_BILLED),P.POL_EFF_DATE,P.POL_EXP_DATE
FROM ACCOUNTDETAIL A
INNER JOIN REGISTER R ON A.ACCOUNT_SET = R.PORTFOLIO_SET
AND A.POLICY_NUMBER = R.POLICY_NUMBER
AND R.POLICY_DATE_TIME =
(SELECT MAX (R2.POLICY_DATE_TIME)
FROM REGISTER R2 WHERE R2.PORTFOLIO_SET =R.PORTFOLIO_SET
AND R2.POLICY_NUMBER = R.POLICY_NUMBER)
INNER JOIN POLICY P ON R.POLICY_NUMBER = P.POLICY_NUMBER AND R.POLICY_DATE_TIME = P.POLICY_DATE_TIME
WHERE AND DETAIL_AMOUNT <> 0 AND A.POLICY_NUMBER NOT LIKE 'M%'
GROUP BY A.POLICY_NUMBER,A.ACCOUNT_SET,P.POL_EFF_DATE,P.POL_EXP_DATE
ORDER BY A.POLICY_NUMBER, A.ACCOUNT_SET

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-27 : 08:57:30

There are several syntax errors in your code - for example, you cannot use a where clause as part of the summation - use case expression instead. THe query below compiles, but I don't know if that is what you logically need

SELECT  A.POLICY_NUMBER ,
        --A.ACCOUNT_SET ,
        SUM(CASE WHEN ACTIVITY_GROUP = 'P' THEN A.DETAIL_AMOUNT
                 ELSE 0
            END) AS TOTAL_AMOUNT_PAYMENT ,
        SUM(CASE WHEN ACTIVITY_GROUP = 'B' THEN A.DETAIL_AMOUNT
                 ELSE 0
            END) AS TOTAL_BILLED 
        --,P.POL_EFF_DATE ,
        --P.POL_EXP_DATE
FROM    ACCOUNTDETAIL A
        INNER JOIN REGISTER R ON 
			A.ACCOUNT_SET = R.PORTFOLIO_SET
			AND A.POLICY_NUMBER = R.POLICY_NUMBER
			AND R.POLICY_DATE_TIME = ( SELECT
										  MAX(R2.POLICY_DATE_TIME)
										FROM
										  REGISTER R2
										WHERE
										  R2.PORTFOLIO_SET = R.PORTFOLIO_SET
										  AND R2.POLICY_NUMBER = R.POLICY_NUMBER
									  )
        INNER JOIN POLICY P ON R.POLICY_NUMBER = P.POLICY_NUMBER
                               AND R.POLICY_DATE_TIME = P.POLICY_DATE_TIME
WHERE   DETAIL_AMOUNT <> 0
        AND A.POLICY_NUMBER NOT LIKE 'M%'
GROUP BY A.POLICY_NUMBER 
        --,A.ACCOUNT_SET 
        --,P.POL_EFF_DATE ,
        --P.POL_EXP_DATE
ORDER BY A.POLICY_NUMBER ,
        A.ACCOUNT_SET

divan
Posting Yak Master

153 Posts

Posted - 2013-06-27 : 09:08:45

Thank you very much ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 10:43:49

if you want all columns in select as you've now it should be this


SELECT m.POLICY_NUMBER,m.ACCOUNT_SET,m.TOTAL_AMOUNT_PAYMENT,m.TOTAL_BILLED,
P.POL_EFF_DATE,P.POL_EXP_DATE
FROM
(
SELECT A.POLICY_NUMBER, A.ACCOUNT_SET,R.POLICY_DATE_TIME,
SUM(CASE WHEN ACTIVITY_GROUP = 'P' THEN A.DETAIL_AMOUNT ELSE 0 END) AS TOTAL_AMOUNT_PAYMENT,
SUM(CASE WHEN ACTIVITY_GROUP = 'B' THEN A.DETAIL_AMOUNT ELSE 0 END) AS TOTAL_BILLED
FROM ACCOUNTDETAIL A
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY PORTFOLIO_SET,POLICY_NUMBER ORDER BY POLICY_DATE_TIME DESC) AS Seq,*
            FROM REGISTER
           ) R 
ON A.ACCOUNT_SET = R.PORTFOLIO_SET 
AND A.POLICY_NUMBER = R.POLICY_NUMBER 
AND R.Seq=1
WHERE DETAIL_AMOUNT <> 0 AND A.POLICY_NUMBER NOT LIKE 'M%'
GROUP BY A.POLICY_NUMBER,A.ACCOUNT_SET
)m
INNER JOIN POLICY P 
ON m.POLICY_NUMBER = P.POLICY_NUMBER 
AND m.POLICY_DATE_TIME = P.POLICY_DATE_TIME
ORDER BY m.POLICY_NUMBER,m.ACCOUNT_SET

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources