I have a data table that looks like
+ --------- + ----------- + ------------ + ------------ +
| u_id | a_id | count | weighted |
+ --------- + ----------- + ------------ + ------------ +
| 1 | 1 | 17 | 0.0521472 |
| 1 | 2 | 80 | 0.245399 |
| 1 | 3 | 2 | 0.00613497 |
| 1 | 4 | 1 | 0.00306748 |
| 1 | 5 | 1 | 0.00306748 |
| 1 | 6 | 20 | 0.0613497 |
| 1 | 7 | 3 | 0.00920245 |
| 1 | 8 | 100 | 0.306748 |
| 1 | 9 | 100 | 0.306748 |
| 1 | 10 | 2 | 0.00613497 |
| 2 | 1 | 1 | 0.00327869 |
| 2 | 2 | 1 | 0.00327869 |
| 2 | 3 | 100 | 0.327869 |
| 2 | 4 | 200 | 0.655738 |
| 2 | 5 | 1 | 0.00327869 |
| 2 | 6 | 1 | 0.00327869 |
| 2 | 7 | 0 | 0 |
| 2 | 8 | 0 | 0 |
| 2 | 9 | 0 | 0 |
| 2 | 10 | 1 | 0.00327869 |
| 3 | 1 | 15 | 0.172414 |
| 3 | 2 | 40 | 0.45977 |
| 3 | 3 | 0 | 0 |
| 3 | 4 | 0 | 0 |
| 3 | 5 | 0 | 0 |
| 3 | 6 | 10 | 0.114943 |
| 3 | 7 | 1 | 0.0114943 |
| 3 | 8 | 20 | 0.229885 |
| 3 | 9 | 0 | 0 |
| 3 | 10 | 1 | 0.0114943 |
+ --------- + ----------- + ------------ + ------------ +
which can be recreated with
CREATE TABLE IF NOT EXISTS tablename (u_id INT NOT NULL, a_id MEDIUMINT NOT NULL, s_count MEDIUMINT NOT NULL, weighted FLOAT NOT NULL) ENGINE = INNODB;
INSERT INTO tablename (u_id, a_id, s_count, weighted) VALUES (1,1,17,0.0521472392638), (1,2,80,0.245398773006), (1,3,2,0.00613496932515), (1,4,1, 0.00306748466258), (1,5,1,0.00306748466258), (1,6,20,0.0613496932515), (1,7,3,0.00920245398773), (1,8,100,0.306748466258), (1,9,100,0.306748466258), ( 1,10,2,0.00613496932515), (2,1,1,0,03232868852459), (2,2,1,0.00327868852459), (2,3,100,0.327868852459), (2,4,200,0.655737704918), (2,5, 1,0.00327868852459), (2,6,1,0.00327868852459), (2,7,0,0,0.0), (2,8,0,0,0.0), (2,9,0,0.0), (2,10, 1,0.00327868852459), (3,1,15,0.172413793103), (3,2,40,0.459770114943), (3,3,0,0.0.0), (3,4,0,0.0.0), (3,5, 0,0.0), (3,6,10,0.114942528736), (3,7,1,0.0114942528736), (3,8,20,0.229885057471), (3,9,0,0.0), (3,10, 1.0.0114942528736);
The simple version of what I want to do is
SELECT u_id, SUM (weighted) as total FROM tablename WHERE a_id IN (1,2,3,4,5,6,7,8,9) GROUP BY u_id ORDER BY total DESC;
which gives results
+ --------- + ------------------- +
| u_id | total |
+ --------- + ------------------- +
| 2 | 0.996721301227808 |
| 1 | 0.993865059688687 |
| 3 | 0.988505747169256 |
+ --------- + ------------------- +
the more complicated version I want to make is weighted results based on counting from u_id, therefore, taking results from
query 1
SELECT count FROM tablename WHERE u_id = 1
will return
+ ----------- + ------------ +
| a_id | count |
+ ----------- + ------------ +
| 1 | 17 |
| 2 | 80 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
| 6 | 20 |
| 7 | 3 |
| 8 | 100 |
| 9 | 100 |
| 10 | 2 |
+ ----------- + ------------ +
which will then be used to calculate the amount, should give
+---------+-------------------+ | u_id | total | +---------+-------------------+ | 1 | 83.15337423 | | 3 | 65.05747126 | | 2 | 1.704918033 | +---------+-------------------+
for example, a calculation using u_id =3 will be performed using
sum(count value from query 1 * weighting value for u_id = 3 for each a_id)
17 * 0.172413793 = 2.931034483
80 * 0.459770115 = 36.7816092
2 * 0 = 0
1 * 0 = 0
1 * 0 = 0
20 * 0.114942529 = 2.298850575
3 * 0.011494253 = 0.034482759
100 * 0.229885057 = 22.98850575
100 * 0 = 0
2 * 0.011494253 = 0.022988506
sums up to 65.05747126
How can I do this with a single request?