Aggregate in SQL Server

Question

Hello everyone, I need help. I need to SUM the result of the query below: 75000 + 1200 + 560 = 76,760.00 How can I get 76,760?

Rijwan Ansari · Answer

In your case: select SUM(cast((select max(biggesquote) from (values (supplier1_price),(supplier2_price),(supplier3_price)) as quoteAmount(biggestQuote))*quantity as biggestquote)) from rs_requisitionslip_details where rs_no='RS-0000003'

Rijwan Ansari · Answer

You can use sum function.. sample in your case select SUM((select max(biggesquote) from (values (supplier1_price),(supplier2_price),(supplier3_price)) as quoteAmount(biggestQuote))*quantity as biggestquote) from rs_requisitionslip_details where rs_no='RS-0000003" Try this

Nikunj Satasiya · Answer

Hi, You can try this solution SELECT SUM (BiggestQuote) FROM ( SELECT CAST ( Select Max (BiggestQuote) from values (supplier1_price),(supplier2_price,(supplier3_price)) As QuoteAmaount(BiggestQuote))*quantity as BiggestQuoate) AS INT ) AS BiggestQuote from [dbo].[rs_RequisitionSlip_Details] where rs_no= 'Rs-0000003' ) tbl

Satya Karki · Answer

Hi, instead of writing long query try this and let us know. select SUM ( cast (( select max (biggesquote) from ( values (supplier1_price),(supplier2_price),(supplier3_price)) as quoteAmount(biggestQuote))*quantity as biggestquote)) from rs_requisitionslip_details where rs_no= 'RS-0000003'

Rijwan Ansari · Answer

Try this way. ..SUM(CAST(...

Sachin Singh · Answer

cast the result to INT SELECT CAST ( Select Max (BiggestQuote) from values (supplier1_price),(supplier2_price,(supplier3_price)) As QuoteAmaount(BiggestQuote))*quantity as BiggestQuoate) AS INT ) AS BiggestQuote from [dbo] [rs_RequisitionSlip_Details] where rs_no= 'Rs-0000003'

Jes Sie · Answer

So far, below is what I did. It serves the purpose though the query is quite long declare @table1 table ( rs_no nvarchar(50), BiggestQuote decimal (18,2) ) insert into @table1 SELECT rs_no, ( SELECT MAX (BiggestQuote) FROM ( VALUES (supplier1_price),(supplier2_price),(supplier3_price)) AS QuoteAmount(BiggestQuote)) * quantity AS BiggestQuote FROM [dbo].[rs_RequisitonSlip_Details] DECLARE @table2 table ( rs_no nvarchar(50), BiggestQuote decimal (18,2) ) insert into @table2 select rs_no, BiggestQuote from @table1 SELECT rs_no, sum (BiggestQuote) FROM @table2 GROUP BY rs_no

Jes Sie · Answer

thanks Sachin for your response also. Tried your code but it also gives me an error

Jes Sie · Answer

Hi, Rijwan thanks for your response. I tried your code but it has an error: