How to display correct sum () value based on type
TABLE 1
STAFF_NO TYPE AMOUNT
A1 O 300
B3 A 10
A1 A 45
C3 O 70
C4 A 300
D4 O 100
TABLE2
STAFF_NO CODE
A1 SS1
B3 SS1
C3 SS2
C4 SS2
D4 SS2
I am internally concatenating 2 tables and selecting code, count and sum for both types for "O" and "A" based on code. Here is my SQL statement:
SELECT DISTINCT B.CODE,
COUNT(DISTINCT B.CODE) AS HEADCOUNT,
O=(SELECT SUM(AMOUNT) FROM TABLE1 WHERE TYPE ='O'),
A=(SELECT SUM(AMOUNT) FROM TABLE1 WHERE TYPE ='A')
FROM TABLE1 A INNER JOIN TABLE2 B ON A.STAFF_NO=B.STAFF_NO group by B.code
My mistake: SS1 and SS2 on "O" and "A" have the same meaning.
CODE HEADCOUNT O A
SS1 2 300 55
SS2 3 300 55
The result I should get:
CODE HEADCOUNT O A
SS1 2 300 55
SS2 3 170 300
+3
source to share
1 answer
GROUP BY
- your friend.
SELECT
B.CODE,
COUNT(A.STAFF_NO) HEADCOUNT,
SUM(CASE A.TYPE WHEN 'O' THEN A.AMOUNT ELSE 0 END) O,
SUM(CASE A.TYPE WHEN 'A' THEN A.AMOUNT ELSE 0 END) A
FROM
TABLE1 A
INNER JOIN TABLE2 B ON B.STAFF_NO = A.STAFF_NO
GROUP BY
B.CODE
If A.AMOUNT
can contain NULL, use ISNULL
:
SUM(CASE A.TYPE WHEN 'O' THEN ISNULL(A.AMOUNT, 0) ELSE 0 END) O
+5
source to share