i need to use the sum of the value in 1 column as part of a ref in another column in a view

Question

hi, i have a view as below.

SELECT     TOP (100) PERCENT 'SA' AS Doc_Type1, 'A' + SUBSTRING('000000', 1, 6 - LEN(CAST(dbo.companies.companyId AS varchar(10)))) 
                      + CAST(dbo.companies.companyId AS varchar(10)) AS Client_ID, 1200 AS Bank_Nom, 0 AS Department, CONVERT(nvarchar(20), 
                      dbo.invoices.invoiceDatePaid, 103) AS Cleared_Date, 'Bacs' AS Payment_type, dbo.invoices.invoiceId, dbo.invoices.invoiceTotal AS Value, '9' AS vat, 
                      ' ' AS bllank, 1 AS Ex_rate
FROM         dbo.invoices INNER JOIN
                      dbo.companies ON dbo.invoices.invoiceCompanyId = dbo.companies.companyId
WHERE     (dbo.invoices.invoiceDatePaid >= DATEDIFF(DAY, 1, CURRENT_TIMESTAMP)) AND (dbo.invoices.invoiceDatePaid < DATEDIFF(DAY, 0, 
                      CURRENT_TIMESTAMP)) AND (dbo.companies.companyPaymentType = 3)
ORDER BY dbo.invoices.invoiceId DESC

In the Payment_Type column i want to add the SUM of the Value column to the word 'Bacs' so it reads 'Bacs £sum' to 2 decimal places. Could you help please, regards and thanks for all the help and suggestions already provided

Answer 1

I'm not very much clear to what exactly you need. You need complete sum of entire column to be displayed or in a group manner, but here is what you can try..Add the sum(Value) after converting it to varchar,which is necessary as two data types has to be varchar to concenate and group by all the remaining columns..something like this

....

,'Bacs'+cast(sum(dbo.invoices.invoiceTotal) as varchar) AS Payment_type

...

group by all remaining columns

...

order by clause

I don't know whether it even close to what you need but it's just a try to help :-)