CODEWITHSUNDEEP
sqlsql-serverjoinaggregate-functions
greener
greener

Reputation: 5069

Recursively aggregate SQL columns

I have two tables and I'm trying to build a query which would do JOIN and a SUM of all numerical columns (profit/loss), and then a SUM again for all profits, and all losses.

Maybe I've poorly designed my tables - any guidance is appreaciated.

Right now, I have:

Table: COMPANY

C_ID  |  NAME
-------------------
 1    |  Abc

Table: FINANCIAL

ID  |  COMPANY_ID  |  INCOME1  |  INCOME2  | COST1  |  COST2
------------------------------------------------------------
1          1            200
2          1                        50
3          1                                   5
4          1                                   3
5          1                                             40

The output I'm seeking is something like (where CASH has a fixed starting value of 100)

C_ID  |  NAME  |  INCOME1  |  INCOME2  | COST1  |  COST2  |  CASH
------------------------------------------------------------------
 1       Abc       200          50         8        40       302

What I've tried:

SELECT
    C.C_ID, C.NAME,
    SUM(F.INCOME1), SUM(F.INCOME2),
    SUM(F.COST1), SUM(F.COST2),
    (100 +
            (SELECT SUM(F.INCOME1), SUM(F.INCOME2) FROM FINS F)
          - (SELECT SUM(F.COST1), SUM(F.COST2) FROM FINS F)
    )
FROM COMP C
INNER JOIN FINS F
ON C.C_ID = F.COMPANY_ID
GROUP BY C.C_ID, C.NAME

Upvotes: 0

Views: 43

Answers (2)

M.Ali
M.Ali

Reputation: 69524

Test Data

DECLARE @Company TABLE(C_ID INT,NAME VARCHAR(10))
INSERT INTO @Company VALUES
(1,'Abc')

DECLARE @FINANCIAL TABLE
(ID INT,COMPANY_ID INT,INCOME1 INT,INCOME2 INT,COST1 INT,COST2 INT)
INSERT INTO @FINANCIAL VALUES
(1,1,200,NULL,NULL,NULL),
(2,1,NULL,50 ,NULL,NULL),
(3,1,NULL,NULL,  5,NULL),
(4,1,NULL,NULL,  3,NULL),
(5,1,NULL,NULL,NULL,40)

Query

SELECT *, INCOME1+INCOME2-COST1-COST2 AS CASH
FROM (
SELECT  C.C_ID
       ,C.NAME
       ,SUM(F.INCOME1) AS INCOME1
       ,SUM(F.INCOME2) AS INCOME2
       ,SUM(F.COST1)   AS COST1
       ,SUM(F.COST2)   AS COST2
FROM @Company C INNER JOIN @FINANCIAL F
ON C.C_ID = F.COMPANY_ID
GROUP BY C.C_ID
       ,C.NAME
      ) Q

Result Set

╔══════╦══════╦═════════╦═════════╦═══════╦═══════╦══════╗
║ C_ID ║ NAME ║ INCOME1 ║ INCOME2 ║ COST1 ║ COST2 ║ CASH ║
╠══════╬══════╬═════════╬═════════╬═══════╬═══════╬══════╣
║    1 ║ Abc  ║     200 ║      50 ║     8 ║    40 ║  202 ║
╚══════╩══════╩═════════╩═════════╩═══════╩═══════╩══════╝

Working SQL FIDDLE

Upvotes: 1

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

TRY THIS

SELECT C.C_ID, C.NAME, F.INCOME1, F.INCOME2, F.COST1, F.COST2, ((100 + F.INCOME1 + F.INCOME2) - (F.COST1 + F.COST2)) AS CASH
FROM COMPANY C
INNER JOIN
(
    SELECT COMPANY_ID, SUM(INCOME1) AS INCOME1, SUM(INCOME2) AS INCOME2
    , SUM(COST1) AS COST1, SUM(COST2) AS COST2
    FROM FINANCIAL
    GROUP BY COMPANY_ID
) F ON C.C_ID = F.COMPANY_ID

Upvotes: 0

Related Questions