----- Original Message ----- From: "Kevin Cagle" <[EMAIL PROTECTED]> To: "MySql List" <[EMAIL PROTECTED]> Sent: Friday, November 19, 2004 10:46 PM Subject: summing from multiple tables
> Greetings, fellow MySQLers! > > I know I ain't doing something right, but I don't know what. Say I have > a website. It has pages on it that get viewed. The hosting company > bills me each day for pages. So.. > > mysql> select * from bill; > +--------+------+ > | amount | day | > +--------+------+ > | 1.10 | mon | > | 2.20 | tue | > | 3.30 | wed | > | 4.40 | thu | > | 5.50 | fri | > | 6.60 | sat | > | 7.70 | sun | > +--------+------+ > 7 rows in set (0.00 sec) > > mysql> select * from pageviewed; > +----------+------------------+ > | page | totaltimesviewed | > +----------+------------------+ > | example1 | 4 | > | example2 | 1 | > | example3 | 4 | > | example4 | 2 | > | example5 | 1 | > +----------+------------------+ > 5 rows in set (0.00 sec) > > mysql> select sum(amount) from bill; > +-------------+ > | sum(amount) | > +-------------+ > | 30.80 | > +-------------+ > 1 row in set (0.01 sec) > > mysql> select sum(totaltimesviewed) from pageviewed; > +-----------------------+ > | sum(totaltimesviewed) | > +-----------------------+ > | 12 | > +-----------------------+ > 1 row in set (0.00 sec) > > So far so good...but when I do this query, I get this result: > > mysql> select sum(amount), sum(totaltimesviewed) from bill, pageviewed; > +-------------+-----------------------+ > | sum(amount) | sum(totaltimesviewed) | > +-------------+-----------------------+ > | 154.00 | 84 | > +-------------+-----------------------+ > 1 row in set (0.00 sec) > > > Why don't I get "30.80" and "12"? How can I change the query so I do > get that result? What in the world did I actually do to get "154.00" > and "84"? I can't see how anything adds up to those numbers... > Your final query, the one that doesn't give you the expected result, is called a join. Joins are discussed in the MySQL Manual; I suggest you review the concept and the examples there. In a nutshell, a join is supposed to be used to combine two tables of *related* data. For instance, one table might be have information about departments in a company while a second table has information about employees in those departments; if these two tables have something in common, like a department number, it makes sense to join them. In your case, you have two tables that contain completely unrelated information and it makes no sense to join them. The reason you are getting the numbers you are seeing is that you are attempting to join two tables that are unrelated *plus* you have omitted the "join clause" that identifies what they have in common. But in this case there is no join clause you could possibly write that would get you the desired answer. The solution to your problem is simple: don't attempt to do the final query. Each of your existing queries are perfectly fine the way they are and give sensible and meaningful answers. Simply run the 3rd and 4th queries as two separate queries rather than trying to combine them into one query. It's that easy. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
