sql query to create columns with arithmetic expres

问题:

I have my sql tables and query as shown below :

CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);

INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
INSERT #ABC VALUES (2013,2,3);

INSERT #DEF VALUES (2013,1,4);
INSERT #DEF VALUES (2013,1,5);
INSERT #DEF VALUES (2013,2,6);

INSERT #GHI VALUES (2013,1,7);
INSERT #GHI VALUES (2013,1,8);
INSERT #GHI VALUES (2013,2,9);
INSERT #GHI VALUES (2013,3,10);

My current query is

SELECT T.[Year],
       T.[Month]
       -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
       ,
       (SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Stores],
       (SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_SalesStores],
       (SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Products]
FROM   (
       -- this selects a list of all possible dates.
       SELECT [Year],
              [Month]
       FROM   #ABC
       UNION
       SELECT [Year],
              [Month]
       FROM   #DEF
        UNION
        SELECT [Year],
               [Month]
        FROM   #GHI) AS T; 

Which returns

+------+-------+------------+-----------------+--------------+
| Year | Month | Sum_Stores | Sum_SalesStores | Sum_Products |
+------+-------+------------+-----------------+--------------+
| 2013 |     1 | 3          | 9               |           15 |
| 2013 |     2 | 3          | 6               |            9 |
| 2013 |     3 | NULL       | NULL            |           10 |
+------+-------+------------+-----------------+--------------+

What I want to do is to add two more columns to my query which shows

Sum_SalesStores/Sum_Products & Sum_SalesStores/Sum_Stores per each month and then sort the query based on the two expressions. Can anyone tell me how its possible ?

回答1:

One way would just be to chuck your entire existing query into a CTE then you can select from that and perform the calculations.

;WITH CTE
     AS (
        /*Paste your existing query*/
        )
SELECT *,
       Sum_SalesStores / Sum_Products AS Foo,
       Sum_SalesStores / Sum_Stores   AS Bar
FROM   CTE
ORDER  BY Foo,
          Bar 

回答2:

I re-factored your code a bit to place the core of the logic in the FROM statement.

SELECT Dates.[Year]
    ,Dates.[Month]
    ,SumStores
    ,SumSalesStores
    ,SumProducts
    ,SumSalesStores/SumProducts
    ,SumSalesStores/SumStores
FROM
    (
        -- This selects a list of all possible dates.
        SELECT [Year],[Month] FROM ABC
        UNION SELECT [Year],[Month] FROM DEF
        UNION SELECT [Year],[Month] FROM GHI
    ) AS Dates
    Left Join
    (
        SELECT [Year]
            ,[Month]
            ,SumStores = Sum(Stores)
        FROM   ABC
        GROUP BY [Year]
            ,[Month]
    ) As Stores
    On Dates.[Year] = Stores.[Year]
    And Dates.[Month] = Stores.[Month]
    Left Join
    (
        SELECT [Year]
            ,[Month]
            ,SumSalesStores = Sum(SalesStores)
        FROM   DEF
        GROUP BY [Year]
            ,[Month]
    ) As SalesStores
    On Dates.[Year] = SalesStores.[Year]
    And Dates.[Month] = SalesStores.[Month]
    Left Join
    (
        SELECT [Year]
            ,[Month]
            ,SumProducts = Sum(Products)
        FROM   GHI
        GROUP BY [Year]
            ,[Month]
    ) As Products
    On Dates.[Year] = Products.[Year]
    And Dates.[Month] = Products.[Month]
ORDER BY 1, 2

Here's the SQL Fiddle.