Kingsley Chew Kingsley Chew - 3 months ago 9
SQL Question

SQL SUM different value only

I have this query:

SELECT * FROM (
SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4
FROM UserInfo_1 AS u
LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION

SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4
FROM UserInfo_2 AS u
LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION

SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4
FROM UserInfo_3 AS u
LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION

SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4
FROM UserInfo_4 AS u
LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3)
LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3)
) AS a
WHERE UID IN (378358,378359)


The result:

UID Char1 Char2 Char3 Fame1 Fame2 Fame3 Fame4
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
378358 46497 130 40365 NULL 10 8839 NULL
378358 46497 130 40365 NULL 2470 8839 NULL
378359 36935 121 123 NULL 17285 NULL 292
378359 36935 121 123 NULL 17285 NULL 1277


As you can see the result above all data are same except the columns Fame4 for
UID=378359
and Fame2 for
UID=378358
.

The question is how can I SUM the different values (only in Fame columns) ONLY and return as 1 row for each
UID
.

the desired result:

UID Char1 Char2 Char3 Fame1 Fame2 Fame3 Fame4
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
378358 46497 130 40365 NULL 2480 8839 NULL
378359 36935 121 123 NULL 17285 NULL 1569

Answer

If you need to SUM only different values use SUM(DISTINCT columnname):

SELECT  UID,
        Char1,
        Char2,
        Char3,
        SUM(DISTINCT Fame1) Fame1,
        SUM(DISTINCT Fame2) Fame2,
        SUM(DISTINCT Fame3) Fame3,
        SUM(DISTINCT Fame4) Fame4
FROM (
--Here comes your sub-query
) AS a
WHERE UID IN (378358,378359)
GROUP BY UID, Char1, Char2, Char3,