Q:

Select from four mysql table with count and sum columns and where clause

I have 5 tables in my schema.
First is opn:
| opnID | submitID | emailID |       opnDate       | invalidOPN |
+-------+----------+---------+---------------------+------------+
|   1   |    6     |    1    | 2014-10-15 11:45:50 |      2     |
|   2   |    6     |    2    | 2014-10-15 11:55:52 |      0     |
|   3   |    6     |    3    | 2014-10-15 12:41:52 |     10     |
|   4   |    7     |    2    | 2014-10-15 17:45:22 |      1     |
|   5   |    7     |    3    | 2014-10-16 00:45:55 |      5     |
|   6   |    6     |    5    | 2014-10-16 01:45:11 |      0     |

I also have clk:
| clkID| submitID | emailID |       clkDate        | invalidCLK |
+-------+----------+---------+---------------------+------------+
|   1   |    6     |    1    | 2014-10-15 11:45:55 |      1     |
|   2   |    6     |    2    | 2014-10-15 11:55:59 |      0     |
|   3   |    6     |    3    | 2014-10-15 12:42:52 |      5     |
|   4   |    7     |    3    | 2014-10-15 17:46:12 |      0     |
|   5   |    6     |    5    | 2014-10-16 00:46:55 |      0     |

A users table:
| userID | firstName | secondName |
+--------+-----------+------------+
|   1    |    john   |    smith   |
|   1    |   susan   |    bella   |

A submission table:
| submitID | userID |
+----------+--------+
|    6     |   1    |
|    7     |   2    |

I need to count opn.submitID to get the number of open and count clk.submitID to get the number of clicks and total of invalidclk and invalidopn for each users.
Here are my expected results:
| userID | fName | sName | numberOfOpen | SUM(opn.invalidOPN) | numberOfClicks | SUM(clk.invalidCLK) |
+--------+-------+-------+--------------+---------------------+----------------+---------------------+
|   1    | john  | smith |      4       |          12         |        4       |         6           |
|   2    | susan | bella |      2       |           6         |        1       |         0           |

I tried with those two queries but I don't reach the result that I need 
SELECT users.userID, users.FirstName, users.SecondName, count(opn.submitID) as "Number of Opens", sum(opn.InvalidOPN) as "Number of invalid Opens"

FROM users 

RIGHT JOIN ( submission INNER JOIN opn ON opn.submitID = submission.submitID and OPNDate between "2013-10-01 00:00:00" AND "2014-10-31 23:59:59" ) ON submission.UserID = users.UserID   group by users.userID 

UNION

SELECT users.userID, users.FirstName, users.SecondName, count(clk.submitID) as "Number of clicks", sum(clk.InvalidCLK) as "Number of invalid clicks"
FROM users
RIGHT JOIN ( submission INNER JOIN clk ON clk.submitID = submission.submitID and CLKDate between "2013-10-01 00:00:00" AND "2014-10-31 23:59:59") ON submission.UserID = users.UserID  group by users.userID 

SELECT users.userID, users.FirstName, users.SecondName, count(opn.submitID) as "Number of Opens", sum(opn.InvalidOPN) as "Number of invalid Opens", count(clk.submitID) as "Number of clicks", sum(clk.InvalidCLK) as "Number of invalid clicks"

FROM users, submission, clk, opn 

where opn.submitID = submission.submitID and clk.submitID = submission.submitID 
And CLKDate between "2013-10-01 00:00:00" AND "2014-10-31 23:59:59" 
AND submission.UserID = users.UserID  group by users.userID

Please help me, and show me what I need to change.

A:

The main problem with doing this is that you land up joining the tables against each other and getting every combination of opn and clk records. In such a situation you land up needing to use COUNT(DISTINCT some_field_name) to count the unique values:-
SELECT users.UserId
        COUNT(DISTINCT opn.OPNID),
        COUNT(DISTINCT clk.CLKID)
FROM users
LEFT OUTER JOIN submission ON users.UserId = submission.UserId 
LEFT OUTER JOIN opn ON submission.SubmitID = opn.SubmitID 
LEFT OUTER JOIN clk ON submission.SubmitID = clk.SubmitID 
GROUP BY users.UserId

However that doesn't help in this case as you also need the sum of the invalid___ fields.
As such I would suggest using a couple of sub queries, one for clk and one for opn. These get the counts and the sums grouped by the user id. And the results of these sub queries are joined to the users table.
Something like this:-
SELECT users.UserId,
        users.fName,
        users.sName,
        numberOfOpen,
        COALESCE(invalidopnsum, 0),
        numberOfClicks,
        COALESCE(invalidclksum, 0)
FROM users
LEFT OUTER JOIN
(
    SELECT submission.UserId, COUNT(opn.SubmitID) AS numberOfOpen, SUM(opn.InvalidOPN) AS invalidopnsum
    FROM submission 
    LEFT OUTER JOIN opn ON submission.SubmitID = opn.SubmitID 
    GROUP BY submission.UserId
) opn1
ON users.UserId = opn1.UserId 
LEFT OUTER JOIN
(
    SELECT submission.UserId, COUNT(clk.SubmitID) AS numberOfClicks, SUM(clk.InvalidCLK) AS invalidclksum
    FROM submission 
    LEFT OUTER JOIN clk ON submission.SubmitID = clk.SubmitID 
    GROUP BY submission.UserId
) clk1
ON users.UserId = clk1.UserId 

