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

user_18181403 0
Viewed 47

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

tsql sql-server
3 Answers

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

Update 1: You can have many as you like, best if you have small of column to display:

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`,
  ...
  SUM(case when status_FK = n then value else 0 end) as `statusn`, 
FROM @table
WHERE value > 1500

For complex cases you can look at PIVOT.

user_2246220

You can use PIVOT, a powerful operator.

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
user_108198300

You can try using Join also

SELECT Status_fk
    ,sum(value)
FROM @table t
INNER JOIN (
    VALUES (1)
        ,(2)
    ) X(StatusID) ON t.status_FK = X.StatusID
WHERE value > 1500
GROUP BY status_FK
user_100136990
Related