CODEWITHSUNDEEP
sqlsql-server
Julie
Julie

Reputation: 333

How to do double calculation in SQL?

I am not sure if my question title is meaningful or not. I need to calculate some data.

I only have Sales1 and Sales2 details.

I am stuck at SQL calculation. TotalSales is Sales1 + Sales2.. SST = TotalSales * 6% and GrandTotal is TotalSales + SST.

I need output something like this:

Oh and Sales1 and Sales2 also generate from SUM(amount) from other fields. So, it's calculation over other calculation 

--------------------------------------------------------------------- 
|  Sales1  |  Sales2  |     TotalSales     | SST      | GrandTotal  |
---------------------------------------------------------------------
|    10.00 |   20.00  |   30.00            |     1.80 |     31.80   |
|    50.00 |   75.90  |  125.90            |    7.554 |    133.454  |
|    20.10 |   55.50  |   75.60            |    4.536 |     80.136  |
---------------------------------------------------------------------

How to generate the SQL? Something like this seems not working.. How to use back TotalSales & SST?

Select sum(amount1) Sales1, 
       sum(amount2) Sales2, 
       (Sales1 + Sales2) TotalSales, 
       ((Sales1 + Sales2) * 6/100) SST, 
       ((Sales1 + Sales2) + ((Sales1 + Sales2) * 6/100)) GrandTotal 
from sales_tbl;

Upvotes: 1

Views: 982

Answers (2)

apomene
apomene

Reputation: 14389

You need to use a sub-query. Try something like:

SELECT subquery.Sales1
      ,subquery.Sales2
      ,(subquery.Sales1 + subquery.Sales2) TotalSales
      ,((subquery.Sales1 + subquery.Sales2) * 6 / 100) SST
      ,((subquerySales1 + subquery,Sales2) + ((subquery,Sales1 + subquery,Sales2) * 6 / 100)) GrandTotal
FROM (
       SELECT sum(amount1) Sales1
             ,sum(amount2) Sales2
       FROM sales_tbl
    ) subquery

Upvotes: 0

jarlh
jarlh

Reputation: 44696

Wrap the first step query up as a derived table, to calculate sales1 and sales2. Then do the rest of the calculations on its result:

select sales1, sales2,
       (Sales1 + Sales2) TotalSales, 
       ((Sales1 + Sales2) * 6/100) SST, 
       ((Sales1 + Sales2) + ((Sales1 + Sales2) * 6/100)) GrandTotal 
from
(
    select sum(amount1) Sales1, sum(amount2) Sales2
    from sales_tbl
) dt

Upvotes: 4

Related Questions