CODEWITHSUNDEEP
mysqlsql
Alan Tingey
Alan Tingey

Reputation: 971

MySQL "Group By" from two joined queries

I have produced the query below which returns the following results:

mysql> select u.username, s.campaignno, if(f.hometeamscore>f.awayteamscore,1,0) as Win, f.hometeamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.hometeamid and s.user_id = u.id union all
    -> select u.username, s.campaignno, if(f.awayteamscore>f.hometeamscore,1,0) as Win, f.awayteamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.awayteamid and s.user_id = u.id;
+------------+------------+-----+-------+
| username   | campaignno | Win | Goals |
+------------+------------+-----+-------+
| tingeyal   |   34910256 |   1 |     5 |
| shanu      |   35410256 |   1 |     4 |
| tingeyal   |   34910256 |   0 |     0 |
| kOS        |   35210256 |   1 |     2 |
| kOS        |   35210256 |   0 |     0 |
| shanu      |   35410256 |   1 |     3 |
| kriste8403 |   35510256 |   1 |     3 |
| kriste8403 |   35510256 |   0 |     0 |
+------------+------------+-----+-------+
8 rows in set (0.00 sec)

However, I wish to return just one line for each username & campaignno combination summing both the "Win" and "Goals" columns. In the above instance I would like the query to return the following:

+------------+------------+-----+-------+
| username   | campaignno | Win | Goals |
+------------+------------+-----+-------+
| tingeyal   |   34910256 |   1 |     5 |
| shanu      |   35410256 |   2 |     7 |
| kOS        |   35210256 |   1 |     2 |
| kriste8403 |   35510256 |   1 |     3 |
+------------+------------+-----+-------+
4 rows in set (0.01 sec)

I believe this will involve the group by? Or maybe I have to create a new query and combine it with this one. As you can see I am unsure on what my next steps should be. If anyone thinks it would be helpful to have the table details then just let me know.

Many thanks.

Upvotes: 1

Views: 71

Answers (1)

A J
A J

Reputation: 4024

I believe this will involve the group by?

Yes, it will. To get the desired result, you don't have to write separate query. Try following.

SELECT tablea.username, tablea.campaignno, SUM(tablea.Win) Win, SUM(tablea.Goals) Goals 
FROM
(select u.username, s.campaignno, if(f.hometeamscore>f.awayteamscore,1,0) as Win, f.hometeamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.hometeamid and s.user_id = u.id 
union all
select u.username, s.campaignno, if(f.awayteamscore>f.hometeamscore,1,0) as Win, f.awayteamscore as Goals from straightred_fixture f, straightred_userselection s, auth_user u where s.fixtureid = f.fixtureid and s.teamselectionid = f.awayteamid and s.user_id = u.id) AS tablea
WHERE 1
GROUP BY tablea.username

Above query will create a temp table tablea containing data of both sub-queries and fetch required data.

Upvotes: 1

Related Questions