[RESOLVED] Combine Two Queries

nielseno

I have a couple queries on different tables where I do a group by to do some calcs. I would like to combine the queries in some way via a join or something. The calculations I do using "GROUP BY" complicates things (for me at least)

Here's a simplified description of the tables:

t1 is the tournament results table comprised of the fields: teamID, tournamentID, and points.

t2 is the team/year table I use to associate a teamID with the year or season that they participated and has the fields teamID and yearID.

The first query calculates the total points the team has accumulated in its career in the league.

The first query is as follows:
SELECT teamID, sum(points) AS Points FROM t1 GROUP BY teamID

The second query calculates how many years a team has participated in the league.

The second query is as follows:
SELECT teamID, count(teamID) AS Years FROM t2 GROUP BY teamID

Both queries produce an equal number of rows with every teamID in league history showing up once.

I would like to combine the two queries, teamID being equal in the two queries to have one result with the fields teamID, Years, Points.

Additionally, if possible in the same statement, I would like to add one more field being Points/Years to give an average.

tomhath

Should be something like this (untested):


select A.teamID, A.Points, B.Years
from
   (SELECT teamID, sum(points) AS Points FROM t1 GROUP BY teamID) as A
INNER JOIN (SELECT teamID, count(teamID) AS Years FROM t2 GROUP BY teamID) as B
  on A.teamID = B.teamID

nielseno

I tried and got an SQL error. Not really sure why, I tried to simplify the query and got a similar error:

select A.teamID, B.tournamentID
from
   (SELECT * FROM t1) as A
INNER JOIN (SELECT * FROM t2) as B
  on A.teamID = B.teamID

Error:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FROM t1) as A
INNER JOIN (SELECT FROM t2

tomhath

B.tournamentID isn't in t2 according to your first post.

I don't see anything wrong with the first query I posted, but check the column names for each table.

nielseno

Sorry, my table structure is a bit more complicated than my example shows so I had to change it a little for the simplified example using your query strcutre.

The column names are correct, for example, this query worked:

SELECT t1.teamID, t2.tournamentID FROM t1, t2 WHERE t1.teamID = t2.teamID

tomhath

It could be that MySQL doesn't support that kind of join. Maybe try it this way (slower):

select A.teamID, sum(A.Points) as totalPoints,
       (SELECT count(*) AS Years FROM t2 as B where A.teamID = B.teamID GROUP BY B.teamID) as Years
from FROM t1 as A
group by  A.teamID

nielseno

I'm starting to think your original suggestion is the correct one.

For some reason, I cannot get my database to take a subquery in the FROM clause.

I tried the examples on this page:

http://dev.mysql.com/doc/refman/5.0/en/from-clause-subqueries.html

and couldn't get one to work, substituting my table/column names in their queries that I copied in.

Frustrating.

Weedpacket

Just out of interest, what version of MySQL are you using? If by chance it's a particularly old one, then subqueries may not be supported.

nielseno

4.0 and that was the problem. I upgraded and the method used in the first reply worked.