Aggregate in SQL Server

Jes Sie

5y1kLearn iOS Programming

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?

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nikunj Satasiya5y ago
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 Karki5y ago
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'
Jes Sie5y ago
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
Rijwan Ansari5y ago
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'
Jes Sie5y ago
thanks Sachin for your response also. Tried your code but it also gives me an error
Rijwan Ansari5y ago
Try this way.

..SUM(CAST(...
Jes Sie5y ago
Hi, Rijwan thanks for your response. I tried your code but it has an error:
Sachin Singh5y ago
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'
Rijwan Ansari5y ago
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