CODEWITHSUNDEEP
sql-server-2008
user450143
user450143

Reputation: 435

SQL Sub query with Computations

I have 3 tables Tablle1, Table2, Table3. Table1 & Table2 are related by Id1. Table2 & Table3 are related by Id2.

  1. I have the following query which works. How do I get CTotal = C00+C01+C02?

  2. Where do I add the OUTPUT statements to return the values of STOTAL & CTOTAL?

SELECT X.Id1, X.S00, X.S01, X.S02, X.S00 + X.S01 + X.S02 AS STOTAL,
       CEILING(X.S00 / @Size) AS C00, CEILING(X.S01 / @Size) AS C01, 
       CEILING(X.S02 / @Size) AS C02,
       Tble1.Select00, Table1.Select01, Table1.Select02

FROM(
    SELECT Table1.Id1, 
        SUM(CASE WHEN Table1.Select00 = 1 THEN Table3.Num00 ELSE 0 END) AS S00, 
        SUM(CASE WHEN Table1.Select01 = 1 THEN Table3.Num01 ELSE 0 END) AS S01, 
        SUM(CASE WHEN Table1.Select02 = 1 THEN Table3.Num02 ELSE 0 END) AS S02
    FROM  Table1 INNER JOIN
                Table2 ON Table1.Id1 = Table2.Id1 INNER JOIN
            Table3 ON Table2.Id2 = Table3.Id2
        WHERE  (Table1.Id1 = @Id1
    )
    GROUP BY Table1.Id1) AS X INNER JOIN  Table1  ON X.Id1 = Table1.Id1

Upvotes: 0

Views: 89

Answers (2)

a1ex07
a1ex07

Reputation: 37364

You can just add CEILING(X.S00 / @Size) + CEILING(X.S01 / @Size) + ... AS CTotal or use derived table:

SELECT a.*, a.C01+a.C02+a.C03 AS CTotal FROM
(
    [your_old_query]
)a

Another alternative to derived table - CTE(common table expression)

Upvotes: 3

Anthony Accioly
Anthony Accioly

Reputation: 22461

Wrap the query with one more outer select and add it all together.

Upvotes: 0

Related Questions