CODEWITHSUNDEEP
sql-servert-sql
aiuen
aiuen

Reputation: 43

T-SQL get multiple SUM from subquery

It's my first question to such forum. I was looking for answers, but I did not find a similar issue.

This is a sample data from my SQL Server table:

typ field1  field2  field3
--------------------------
IN  johny   bravo   123
IN  mickey  mouse     5
IN  mickey  mouse    70
OUT johny   bravo    20
OUT mickey  mouse    15

What I need to select is:

field1  | field2    |   sum(field3) where typ='IN'  |   sum(field3) where typ='OUT'

That's what I've come up with so far:

SELECT
    t.field1,
    t.field2,
    SUM(t1.total) AS total_in
    SUM(t2.total) AS total_out
FROM
(
    SELECT
        'IN' as typ,
        field1,
        field2,
        SUM(field3) AS total
    FROM XYZ
    LEFT JOIN some_table ON ...
    WHERE condition=1
    GROUP BY typ, field1, field2

    UNION ALL

    SELECT
        'OUT' as typ,
        field1,
        field2,
        SUM(field3) AS total
    FROM XYZ
    LEFT JOIN some_table ON ...
    WHERE condition=2
    GROUP BY typ, field1, field2
) t
LEFT JOIN t t1 ON t.field1 = t1.field1
LEFT JOIN t t2 ON t.field1 = t2.field1
GROUP BY 
    t1.field1,
    t1.field2

But when I run this query I get an error

Msg 206, Level 16, State 1, Line 1
Invalid object name t

and I simply don't know how to get this query to work.

I will be grateful for your suggestions. Thanks.

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want conditional aggregation:

SELECT t.field1,  t.field2,
       SUM(CASE WHEN type = 'IN' THEN field3 ELSE 0 END) AS total_in,
       SUM(CASE WHEN type = 'OUT' THEN field3 ELSE 0 END) AS total_out
FROM . . .
GROUP BY t.field1, t.field2;

You can also do this with pivot.

Upvotes: 1

Related Questions