Question about subqueries with specific conditions and same condition as query at the same time-CodePudding

I am using T-SQL for a few months and there is one thing I simply don't know how to do. Let's say we have a table created below

DECLARE @table TABLE
               (
                   id int,
                   status_FK int,
                   [value] int
               )
 
INSERT INTO @table (id, status_FK, [value])
VALUES (1, 1, 1000),
       (2, 1, 2000),
       (3, 2, 3000),
       (4, 1, 2000),
       (5, 2, 2000),
       (6, 2, 3000),
       (7, 5, 12000)

SELECT
    (SELECT SUM(value) WHERE status_FK = 1) AS 'status1',
    (SELECT SUM(value) WHERE status_FK = 2) AS 'status2'
FROM 
    @table
WHERE 
    value > 1500

What I'm trying to do is use condition value > 1500 for all subqueries but also each subquery have specific condition. Can someone please explain me how to do this simple thing without repeating whole main query in both subqueries with different conditions? Many thanks <3

CodePudding user response：

Probably something like that:

SELECT
  SUM(case when status_FK = 1 then value else 0 end) as 'status1',
  SUM(case when status_FK = 2 then value else 0 end) as 'status2'
FROM @table
WHERE value > 1500

CodePudding user response：

You can try this one

CREATE  TABLE #TEMP
               (
                   id int,
                   status_FK int,
                   [value] int
               )
 
INSERT INTO #TEMP (id, status_FK, [value])
VALUES (1, 1, 1000),
       (2, 1, 2000),
       (3, 2, 3000),
       (4, 1, 2000),
       (5, 2, 2000),
       (6, 2, 3000),
       (7, 5, 12000)



    SELECT [1] AS status1, [2] AS status2
    FROM (SELECT status_FK, value FROM #TEMP WHERE value > 1500) AS T
    PIVOT(SUM([value]) FOR status_FK IN ([1], [2])) AS PT

CodePudding user response：

SELECT id,SUM(value) AS _sum
FROM @table
WHERE status_FK in (1,2)
GROUP BY id
HAVING SUM(value) > 1500

RESULT :