SQLTeam.com | Weblogs | Forums

Unable to SUM totals

Hi everyone

I'm trying to SUM the totals of the INSTALMENT, YEARLYVALUE, EST LIFETIMEVALUE, GIFTAID and TOTAL columns using the report below, but I can't seem to get it to work. I seem to require a multiplication within the SUM function, something which my version of SQL doesn't seem to allow.

Is there an easy way to sum these totals? I'm using an old version of SQL and cannot use some of the more recent totalling functions.

Many thanks
Jon

SELECT SERIALNUMBER,[FIRSTNAME],[SURNAME],[COUNTRY],
(CASE WHEN Row=100000 THEN SPACE(8) ELSE convert(varchar(8),[STARTDATE],3) END) [STARTDATE], [TYPE], [FREQUENCY], [INSTALMENTS], [INSTALMENT], [YEARLYVALUE], [EST LIFETIMEVALUE], [GIFTAID],[TOTAL],[DESTINATION],[MAILINGSEGMENT], [CLASSOF]
FROM
(
select ph.Serialnumber [SERIALNUMBER], co.firstname [FIRSTNAME],co.keyname [SURNAME],co.country [COUNTRY], ed.CLASSOF[CLASSOF], com.NOTES[MAILINGSEGMENT], convert(date,ph.STARTDATE) [STARTDATE],
ph.PAYMENTFREQUENCY [FREQUENCY], ph.INSTALMENTVALUE [INSTALMENT], ph.instalmentvalue12 [YEARLYVALUE], (ph.INSTALMENTVALUE)(count(pi.instalment)) [EST LIFETIMEVALUE],
count(pi.instalment) [INSTALMENTS],
ph.INSTALMENTVALUE [Amount],
ROUND((case when gd.GADStatus='active' AND ph.taxclaimable='Yes' then ph.INSTALMENTVALUE*count(pi.instalment)0.25 else 0 end),2) [GIFTAID],
ROUND((case when gd.GADStatus='active' AND ph.taxclaimable='Yes' then ph.INSTALMENTVALUE
count(pi.instalment)+(ph.INSTALMENTVALUE)*count(pi.instalment)*0.25 else 0 end),2) [TOTAL],
dc.DESTINATIONNOTES [DESTINATION], ph.PaymentType [TYPE],dprof.ccode,row_number() over (order by ph.STARTDATE desc) Row
from PLEDGEHEADER ph
left join contact co on ph.SERIALNUMBER=co.SERIALNUMBER
left join (SELECT serialnumber, (STUFF((SELECT CAST(', ' + parametername AS VARCHAR(MAX))
FROM contactparameter WHERE (serialnumber = contact.serialnumber and parametercategory='Constituent Code')
FOR XML PATH ('')), 1, 2, '')) AS ccode
FROM contact) dprof on ph.SERIALNUMBER=dprof.SERIALNUMBER
left join DESTINATIONCODE dc on ph.DESTINATIONCODE=dc.DESTINATIONCODE
left join GIFTAID_CURRENTDECLARATION gd on ph.SERIALNUMBER=gd.serialnumber

left join PLEDGEINSTALMENT pi on ph.PLEDGEID=pi.PLEDGEID
left join dbo.EDUCATION AS ED on ph.SERIALNUMBER = ed.SERIALNUMBER
left join COMMUNICATION com ON co.SERIALNUMBER = com.SERIALNUMBER
WHERE ed.INSTITUTIONTYPE='Location' AND com.SUBJECT = 'Income' AND dc.destinationtype LIKE 'London%'
and ph.INCOMETYPE='DONATION' and ph.STARTDATE>='01/04/19'
and ph.sourcecode IN ('APP00147', 'APP00149', 'APP00150', 'APP00151', 'APP00152', 'APP00153', 'APP00154', 'APP00155', 'APP00156', 'APP00157', 'APP00158', 'APP00159', 'APP00160', 'APP00161', 'APP00162', 'APP00163',
'APP00164', 'APP00165', 'APP00167', 'APP00168', 'APP00169', 'APP00170', 'APP00172')

GROUP BY ph.SERIALNUMBER, co.FIRSTNAME, co.KEYNAME, co.COUNTRY, ed.CLASSOF, com.NOTES, ph.STARTDATE, ph.PAYMENTFREQUENCY, ph.INSTALMENTVALUE, GADStatus, ph.TAXCLAIMABLE ,
dc.DESTINATIONNOTES , ph.PAYMENTTYPE , dprof.ccode

UNION
select 'TOTALS' serialnumber,' ' firstname,' ' surname,' ' country,' ' classof,' ' lifetime, ' ' frequency,' ' instalment,' ' instalments,' ' yearlyvalue,' ' mailingsegment,' 'startdate,SUM(ph.INSTALMENTVALUE) amount,

SUM(ROUND((case when gd.GADStatus='active' AND ph.taxclaimable='Yes' then ph.INSTALMENTVALUE*0.25 else 0 end),2)) [GIFTAID],
SUM(ph.instalmentvalue) [TOTES],'

' destinationnotes,' , 'type,' ' [Constituent Codes],100000 Row
from PLEDGEHEADER ph
left join contact co
on ph.SERIALNUMBER=co.SERIALNUMBER
left join
(SELECT serialnumber, (STUFF((SELECT CAST(', ' + parametername AS VARCHAR(MAX))
FROM contactparameter WHERE (serialnumber = contact.serialnumber and parametercategory='Constituent Code')
FOR XML PATH ('')), 1, 2, '')) AS ccode
FROM contact) dprof
on ph.SERIALNUMBER=dprof.SERIALNUMBER
left join DESTINATIONCODE dc on ph.DESTINATIONCODE=dc.DESTINATIONCODE
left join GIFTAID_CURRENTDECLARATION gd on ph.SERIALNUMBER=gd.serialnumber

left join PLEDGEINSTALMENT pi on ph.PLEDGEID=pi.PLEDGEID
left join dbo.EDUCATION AS ED on ph.SERIALNUMBER = ed.SERIALNUMBER
left join COMMUNICATION com ON co.SERIALNUMBER = com.SERIALNUMBER
WHERE ed.INSTITUTIONTYPE='Location' AND com.SUBJECT = 'Income' AND dc.destinationtype LIKE 'London%'
and ph.sourcecode IN ('APP00147', 'APP00149', 'APP00150', 'APP00151', 'APP00152', 'APP00153', 'APP00154', 'APP00155', 'APP00156', 'APP00157', 'APP00158', 'APP00159', 'APP00160', 'APP00161', 'APP00162', 'APP00163',
'APP00164', 'APP00165', 'APP00167', 'APP00168', 'APP00169', 'APP00170', 'APP00172')

) dtot

ORDER BY dtot.row

  1. Provide minimum sample data. Just throwing a wall of SQL code does not help by any means
  2. I do not see anything about SUM on the code you posted
  3. At least make it readable using http://www.dpriver.com/pp/sqlformat.htm

Also use the three ticks

SELECT   serialnumber, 
         [FIRSTNAME], 
         [SURNAME], 
         [COUNTRY], ( 
         CASE 
                  WHEN row=100000 THEN Space(8) 
                  ELSE CONVERT(VARCHAR(8),[STARTDATE],3) 
         END) [STARTDATE], 
         [TYPE], 
         [FREQUENCY], 
         [INSTALMENTS], 
         [INSTALMENT], 
         [YEARLYVALUE], 
         [EST LIFETIMEVALUE], 
         [GIFTAID], 
         [TOTAL], 
         [DESTINATION], 
         [MAILINGSEGMENT], 
         [CLASSOF] 
FROM     ( 
                   SELECT    ph.serialnumber [SERIALNUMBER], 
                             co.firstname [FIRSTNAME], 
                             co.keyname [SURNAME], 
                             co.country [COUNTRY], 
                             ed.classof[CLASSOF], 
                             com.notes[MAILINGSEGMENT], 
                             CONVERT(DATE,ph.startdate) [STARTDATE], 
                             ph.paymentfrequency [FREQUENCY], 
                             ph.instalmentvalue [INSTALMENT], 
                             ph.instalmentvalue12 [YEARLYVALUE], 
                             (ph.instalmentvalue)(count(pi.instalment)) [EST LIFETIMEVALUE], 
                             count(pi.instalment) [INSTALMENTS], 
                             ph.instalmentvalue [Amount], 
                             round(( 
                             CASE 
                                       WHEN gd.gadstatus='active' 
                                       AND       ph.taxclaimable='Yes' THEN ph.instalmentvalue*count(pi.instalment)0.25
                                       ELSE 0 
                             END),2) [GIFTAID], 
                             round(( 
                             CASE 
                                       WHEN gd.gadstatus='active' 
                                       AND       ph.taxclaimable='Yes' THEN ph.instalmentvaluecount(pi.instalment)+(ph.instalmentvalue)*count(pi.instalment)*0.25 
                                       ELSE 0 
                             END),2) [TOTAL], 
                             dc.destinationnotes [DESTINATION], 
                             ph.paymenttype [TYPE], 
                             dprof.ccode, 
                             row_number() OVER (ORDER BY ph.startdate DESC) row 
                   FROM      pledgeheader ph 
                   LEFT JOIN contact co 
                   ON        ph.serialnumber=co.serialnumber 
                   LEFT JOIN 
                             ( 
                                    SELECT serialnumber, 
                                           (stuff( 
                                           ( 
                                                  SELECT cast(', ' + parametername AS varchar(max))
                                                  FROM   contactparameter 
                                                  WHERE  ( 
                                                                serialnumber = contact.serialnumber
                                                         AND    parametercategory='Constituent Code') FOR xml path ('')), 1, 2, '')) AS ccode
                                    FROM   contact) dprof 
                   ON        ph.serialnumber=dprof.serialnumber 
                   LEFT JOIN destinationcode dc 
                   ON        ph.destinationcode=dc.destinationcode 
                   LEFT JOIN giftaid_currentdeclaration gd 
                   ON        ph.serialnumber=gd.serialnumber 
                   LEFT JOIN pledgeinstalment pi 
                   ON        ph.pledgeid=pi.pledgeid 
                   LEFT JOIN dbo.education AS ed 
                   ON        ph.serialnumber = ed.serialnumber 
                   LEFT JOIN communication com 
                   ON        co.serialnumber = com.serialnumber 
                   WHERE     ed.institutiontype='Location' 
                   AND       com.subject = 'Income' 
                   AND       dc.destinationtype LIKE 'London%' 
                   AND       ph.incometype='DONATION' 
                   AND       ph.startdate>='01/04/19' 
                   AND       ph.sourcecode IN ('APP00147', 
                                               'APP00149', 
                                               'APP00150', 
                                               'APP00151', 
                                               'APP00152', 
                                               'APP00153', 
                                               'APP00154', 
                                               'APP00155', 
                                               'APP00156', 
                                               'APP00157', 
                                               'APP00158', 
                                               'APP00159', 
                                               'APP00160', 
                                               'APP00161', 
                                               'APP00162', 
                                               'APP00163', 
                                               'APP00164', 
                                               'APP00165', 
                                               'APP00167', 
                                               'APP00168', 
                                               'APP00169', 
                                               'APP00170', 
                                               'APP00172') 
                   GROUP BY  ph.serialnumber, 
                             co.firstname, 
                             co.keyname, 
                             co.country, 
                             ed.classof, 
                             com.notes, 
                             ph.startdate, 
                             ph.paymentfrequency, 
                             ph.instalmentvalue, 
                             gadstatus, 
                             ph.taxclaimable , 
                             dc.destinationnotes , 
                             ph.paymenttype , 
                             dprof.ccode 
                   UNION 
                   SELECT    'TOTALS'                serialnumber, 
                             ' '                     firstname, 
                             ' '                     surname, 
                             ' '                     country, 
                             ' '                     classof, 
                             ' '                     lifetime, 
                             ' '                     frequency, 
                             ' '                     instalment, 
                             ' '                     instalments, 
                             ' '                     yearlyvalue, 
                             ' '                     mailingsegment, 
                             ' '                     startdate, 
                             sum(ph.instalmentvalue) amount, 
                             sum(round(( 
                             CASE 
                                       WHEN gd.gadstatus='active' 
                                       AND       ph.taxclaimable='Yes' THEN ph.instalmentvalue*0.25
                                       ELSE 0 
                             END),2)) [GIFTAID], 
                             sum(ph.instalmentvalue) [TOTES], 
                             ' ' destinationnotes, 
                             ' , 'type, 
                             ' ' [Constituent Codes], 
                             100000 row 
                   FROM      pledgeheader ph 
                   LEFT JOIN contact co 
                   ON        ph.serialnumber=co.serialnumber 
                   LEFT JOIN 
                             ( 
                                    SELECT serialnumber, 
                                           (stuff( 
                                           ( 
                                                  SELECT cast(', ' + parametername AS varchar(max))
                                                  FROM   contactparameter 
                                                  WHERE  ( 
                                                                serialnumber = contact.serialnumber
                                                         AND    parametercategory='Constituent Code') FOR xml path ('')), 1, 2, '')) AS ccode
                                    FROM   contact) dprof 
                   ON        ph.serialnumber=dprof.serialnumber 
                   LEFT JOIN destinationcode dc 
                   ON        ph.destinationcode=dc.destinationcode 
                   LEFT JOIN giftaid_currentdeclaration gd 
                   ON        ph.serialnumber=gd.serialnumber 
                   LEFT JOIN pledgeinstalment pi 
                   ON        ph.pledgeid=pi.pledgeid 
                   LEFT JOIN dbo.education AS ed 
                   ON        ph.serialnumber = ed.serialnumber 
                   LEFT JOIN communication com 
                   ON        co.serialnumber = com.serialnumber 
                   WHERE     ed.institutiontype='Location' 
                   AND       com.subject = 'Income' 
                   AND       dc.destinationtype LIKE 'London%' 
                   AND       ph.sourcecode IN ('APP00147', 
                                               'APP00149', 
                                               'APP00150', 
                                               'APP00151', 
                                               'APP00152', 
                                               'APP00153', 
                                               'APP00154', 
                                               'APP00155', 
                                               'APP00156', 
                                               'APP00157', 
                                               'APP00158', 
                                               'APP00159', 
                                               'APP00160', 
                                               'APP00161', 
                                               'APP00162', 
                                               'APP00163', 
                                               'APP00164', 
                                               'APP00165', 
                                               'APP00167', 
                                               'APP00168', 
                                               'APP00169', 
                                               'APP00170', 
                                               'APP00172') ) dtot 
ORDER BY dtot.row
1 Like

If this is a correct reflection of the code you have:
ph.instalmentvalue * count(pi.instalment)0.25
then it's missing an * :
ph.instalmentvalue * count(pi.instalment) * 0.25

1 Like