SQL Query: SUM on three columns with criteria

Question

Hi,

I have a table with columns like these :

idx | amount | usercol1 | usercol2 | usercol3 | percentage1 | percentage2 | percentage3

Data is typically like this :

0   | 1500   | 1        | null     | null     | 100         | null        | null
1   | 3000   | 2        | 3        | null     | 50          | 50          | null

I would like to make a SUM() of every user's amount.

Example :

• user1= 1500*100/100 (amount*usercol1/100)
• user2= 3000*50/100 (amount*usercol1/100)
• user3= 3000*50/100 (amount*usercol2/100)

I tried UNION to no avail (did not sum the SUMs).

Is there a way to do this ? The problem being that it should GROUP BY the username (which I get with a LEFT OUTER JOIN usernames ON exampletable.usercol1=usernames.idx).

I know this is non standard and would be better with relations from another table. But I am not allowed to change the table structure.

Many many many thanks ! :=)

Hereby, an example that gives a wrong result (seems to give only results from the query in the middle)

( 
SELECT SUM(projects.amount * (projects.percentage1/100)) as totalproj, 
entities.idx as idx, 
COUNT(projects.idx) as numproj, 
entities.name 
 FROM projects 
 INNER JOIN entities ON projects.usercol1=entities.idx 
 WHERE projects.usercol1=entities.idx 
GROUP BY name ORDER BY totalproj DESC 
) 
UNION ALL
( 
SELECT SUM(projects.amount * (projects.percentage2/100)) as totalproj, 
entities.idx as idx, 
COUNT(projects.idx) as numproj, 
entities.name 
 FROM projects 
 INNER JOIN entities ON projects.usercol2=entities.idx 
 WHERE projects.usercol2=entities.idx 
GROUP BY name ORDER BY totalproj DESC 
) 
UNION ALL
( 
SELECT SUM(projects.amount * (projects.percentage3/100)) as totalproj, 
entities.idx as idx, 
COUNT(projects.idx) as numproj, 
entities.name 
 FROM projects 
 INNER JOIN entities ON projects.usercol3=entities.idx 
 WHERE projects.usercol3=entities.idx 
GROUP BY name ORDER BY totalproj DESC 
)
ORDER BY totalproj DESC 
LIMIT 10

Answer 1

You could use a derived table to simulate a first normal form table then join onto that.

SELECT SUM(P.amount * (P.percentage/100)) as totalproj, 
       entities.idx as idx, 
       COUNT(P.idx) as numproj, 
       entities.name 
FROM 
(
SELECT idx, amount, usercol1 AS usercol, percentage1 AS percentage
FROM projects
UNION ALL
SELECT idx, amount, usercol2 AS usercol, percentage2 AS percentage
FROM projects
UNION ALL
SELECT idx, amount, usercol3 AS usercol, percentage3 AS percentage
FROM projects
) P
 INNER JOIN entities ON P.usercol=entities.idx 
 WHERE P.usercol=entities.idx 
 GROUP BY name 
 ORDER BY totalproj DESC 

Answer 2

using this data (i added some stranger data to make sure the math was working properly)

0   1500    1   NULL    NULL    100     NULL    NULL
1   3000    2   3       NULL    50      50      NULL
2   780     4   1       3       70      20      50
3   3800    2   4       1       30      20      10

i got these results

  user  commission
-------  -------------
    1     2036
    2     2640
    3     1890
    4     1306

is this what you were looking for? below is my query

SELECT  [user]
       ,SUM([commission]) AS commission
FROM    ( SELECT    [usercol1] AS [user]
                   ,( [amount] * [percentage1] ) / 100 AS commission
          FROM      [dbo].[projects]
          WHERE     [usercol1] IS NOT NULL
                    AND [percentage1] IS NOT NULL
          UNION ALL
          SELECT    [usercol2]
                   ,( [amount] * [percentage2] ) / 100
          FROM      [dbo].[projects]
          WHERE     [usercol2] IS NOT NULL
                    AND [percentage2] IS NOT NULL
          UNION ALL
          SELECT    [usercol3]
                   ,( [amount] * [percentage3] ) / 100
          FROM      [dbo].[projects]
          WHERE     [usercol3] IS NOT NULL
                    AND [percentage3] IS NOT NULL
        ) x
GROUP BY [user]