count and sum columns from 2 tables.

demetri007

i have 3 columns tbl1.col1, tbl1.col3, and tbl2.col1. tbl1.col1 i want to count the rows, tbl1.col3 i want to sum the values, and tbl2.col1 i want to count rows. i am really at a loss here, please someone help

njm

Assuming MySQL, use the COUNT() and SUM() functions

hth, njm

demetri007

ya i know about those functions, the problem i am having is when i use more than one table. here is my query: "SELECT count(tbl1.serial) AS num_bills, sum(tbl1.denom) AS sum_bills, count(tb2.user_id) AS num_users FROM tbl1, tbl2 ". if i query the db three times i get the correct count of each column, or if i query each table seperatly i get the correct value. the problem arrises when i use the above query, i get much larger values than i should have. the values for these columns are: count(tbl1.serial)=9, sum(tbl1.denom)=118, count(tbl2.user_id)=9. please help :-)

njm

You need to specify how the two tables relate to one another, either with WHERE or a JOIN.

hth, njm

demetri007

hummm... i dont see any relation between the tables. i just want the count of two columns from one table and the sum of the other column from the other table. what i am looking for is a query that will retieve that information in one call. currently i am using two queries, one for each table. is it even possible to count() and/or sum() from more than one table in one call?

njm

OK, I see. I don't think this is possible in SQL. If you include 2 tables with no relationship then you will get all combinations of the rows from each table

njm

demetri007

Thanks njm for your help, much appreciated.