CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Arockia Nirmal
Arockia Nirmal

Reputation: 787

Calculating sum(column) based on other column values in the same table

I have the following table 

Id   quantity    level_1     group_1
1    13          1           1
2    10          1           1
3    10          0           1
4    10          0           1
5    20          1           0

I want to neglect level_1='0' from the above table.

I want to calculate the sum(quantity) of group_1='1' and group_1='0' separately satisfying the first condition above.

Expected result:

sum_quantity   sum_quantity_2_Not_Valid
23             20

Upvotes: 0

Views: 4036

Answers (4)

VDK
VDK

Reputation: 583

This will be the answer, as he wants to exclude level_1='0' :

SELECT SUM(CASE group_1 WHEN 1 THEN quantity ELSE 0 END) AS sum_quantity
     , SUM(CASE group_1 WHEN 0 THEN quantity ELSE 0 END) AS sum_quantity_2_Not_Valid 
FROM table
WHERE level_1 <> 0

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Try like below

Schema:

CREATE TABLE #TAB (Id INT, quantity INT, level_1 INT, group_1 INT)
INSERT INTO #TAB

SELECT 1, 13, 1, 1
UNION ALL
SELECT 2, 10, 1, 1
UNION ALL
SELECT 3, 10, 0, 1
UNION ALL
SELECT 4, 10, 0, 1
UNION ALL
SELECT 5, 20, 1, 0

Use SUM with CASE 

SELECT  SUM(CASE level_1 WHEN 1 THEN quantity ELSE 0 END) AS sum_quantity
, SUM(CASE level_1 WHEN 0 THEN quantity ELSE 0 END) AS sum_quantity_2_Not_Valid 
FROM #TAB
WHERE group_1 =1

And the Result will be

+--------------+--------------------------+
| sum_quantity | sum_quantity_2_Not_Valid |
+--------------+--------------------------+
|           23 |                       20 |
+--------------+--------------------------+

If your intention to get values by grouping group_1

SELECT [1] AS sum_quantity, [0] AS sum_quantity_2_Not_Valid  FROM (
SELECT group_1,
SUM(CASE level_1 WHEN 1 THEN quantity ELSE 0 END) AS sum_quantity
FROM #TAB
GROUP BY group_1
)AS A
PIVOT
(
    MAX( sum_quantity) FOR group_1 IN ([1],[0])
)PVT

Upvotes: 1

Sid
Sid

Reputation: 172

I am sure this would do it :-

SELECT MAX(Valid_Sum) AS Final_Valid_Sum,
       MAX(Invalid_sum) AS Final_InValid_Sum
FROM
(
    SELECT SUM(quantity) Valid_Sum,
           0 AS Invalid_Sum
    FROM Table1
    WHERE level_1 <> 0
          AND group_1 = 1
    UNION
    SELECT 0,
           SUM(Quantity)
    FROM Table1
    WHERE level_1 <> 0
          AND group_1 = 0
);

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Use SUB query to get result :

CREATE TABLE #Table(Id INT,  quantity INT, level_1 INT, group_1 INT)
INSERT INTO #Table(Id ,  quantity , level_1 , group_1)
SELECT 1,13,1,1 UNION ALL
SELECT 2,10,1,1 UNION ALL
SELECT 3,10,0,1 UNION ALL
SELECT 4,10,0,1 UNION ALL
SELECT 5,20,1,0

SELECT SUM(A.quantity) sum_quantity,SUM(B.quantity) sum_quantity_2_Not_Valid 
FROM 
(
   SELECT SUM(quantity) quantity,group_1
   FROM #Table
   WHERE level_1 = 1 AND group_1 = 1
   GROUP BY group_1
 )A,
 (
   SELECT SUM(quantity) quantity,group_1
   FROM #Table
   WHERE level_1 = 1 AND group_1 = 0
   GROUP BY group_1
  )B

Upvotes: 1

Related Questions