February 11, 2009 at 12:19 am
hi.. i have code as below
SELECT DISTINCT
SLRXX_ProductGroup.ProductGroupName ,
SUM(SLDXX_InvoiceDetail.Qty) AS Quantity ,
'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10))) AS Amount ,
FROM
SLDXX_InvoiceDetail
INNER JOIN SLDXX_Invoice ON
SLDXX_InvoiceDetail.InvoiceId = SLDXX_Invoice.InvoiceId
INNER JOIN SLRXX_Product ON
SLDXX_InvoiceDetail.ProductId = SLRXX_Product.ProductId
INNER JOIN SLRXX_ProductGroup ON
SLRXX_Product.ProductGroupId = SLRXX_ProductGroup.ProductGroupId
INNER JOIN SLDXX_PaymentDetail ON
SLDXX_Invoice.InvoiceNo = SLDXX_PaymentDetail.InvoiceNo
FULL OUTER JOIN SLRXX_PackageProduct ON
SLDXX_InvoiceDetail.PackageProductID >= SLRXX_PackageProduct.PackageProductId
WHERE
SLDXX_Invoice.InvoiceStatus = '1' AND
SLDXX_PaymentDetail.PaymentDate = CONVERT(VARCHAR,GETDATE()-1, 101)
GROUP BY
SLRXX_ProductGroup.ProductGroupName
ORDER BY
SLRXX_ProductGroup.ProductGroupName
COMPUTE SUM(SUM(SLDXX_InvoiceDetail.Qty))
the code is right, but how i want to get the total sum from this statement 'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10))) using COMPUTE
February 11, 2009 at 8:02 am
maybe my subject is wrong. i already do a concatenation in this statement
'RM' + (CAST(CAST(SUM(SLDXX_InvoiceDetail.UnitPrice * SLDXX_InvoiceDetail.Qty) AS NUMERIC(9,2)) AS VARCHAR(10)))
below is the output from the code
ProductGroupName Quantity Amount
Media 69RM11568.00
Directory6RM184.90
Registration733RM92650.00
Reader 18RM2709.45
sum
826
i manage to get the total sum for the Quantity column using this statement COMPUTE SUM(SUM(SLDXX_InvoiceDetail.Qty))
but how to get the total sum for the Amount column without remove the 'RM'?
February 12, 2009 at 8:20 am
If you post some sample DDL & DATA we would be happy to help you solve your problem.
Please read this article for more information on how to get the best help. http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply