sum of two row from 2 different table(is this correct)

[Taku]

greetings! is this correct?

 

<?php
$input = mysql_real_escape_string($POST['select'])
$sql = "
SELECT
(
SELECT (quantity)
FROM test_req_concepcion
WHERE id = 1
) AS totalcafe,
(
SELECT (quantity)
FROM test_noval
WHERE id = 1
) AS totalcafe1";
$qry = mysql_query($sql);
$row = mysql_fetch_assoc($qry);

echo $row['totalcafe'] + $row['totalcafe1'];
?>

Edited February 11, 2013 by Taku

[Psycho]

Hmm . . . , that's not making sense to me. You have a POST variable called 'select' which I would think is used to select the appropriate records in your query. But, there is nothing in the query to filter the records. So, you would be selecting ALL the records from both tables. And, I doubt that query would run anyway. And, you know, it takes 2 seconds to drop a query into your database admin panel to test. Why would you take the trouble of asking "does this work" rather than testing it? Of course, if what you think might work doesn't then absolutely post here for help. But, when you simply ask "will this work" it appears you are just being lazy.

 

Please provide some more information as to what is in the tables and what you need to get.

[Taku]

oh sorry about that.. well try to ignore the select above for now..

 

also I did try to test this and it gave me the result I wanted wherein the 1st row of table A is added to the 1st row of table B...

 

but what I wanted to do is to add all the row from table A and B like this...

 

a b

1 2

4 5

... ...

. .

 

wherein it will add 1 and 2 then 4 and 5

 

like this

c

3

9

....

.

 

I am really new at php and I don't know how to do this.. unless like that one by one...

Edited February 11, 2013 by Taku

[Jessica]

How are they related?

[Taku]

by id?

[Psycho]

OK, now I am more confused. You now state you want multiple rows to be returned but your queries are constructed to return only 1 row each. Are you wanting a separate row for each unique ID? So,

 

ID | total1 | total2 | total
1	 1	    2	    3
2	 4	    5	    9

[Jessica]

Use a join, add the two columns together in SQL.

 

Select t1.id, t1.col, t2.col, (t1.col+t2.col) as sum

From t1

Inner join t2

On t1.id = t2.id

[Jessica]

Only don't call it sum, that's a reserved word. My bad.

[Psycho]

If ALL ids exist in both tables then you can do the following to get the quantity totals for each ID from both tables in a single query as follows:

SELECT t1.id, totalCafe1, totalCafe2, totalCafe1 + totalCafe2 as totalCafe
FROM
(
SELECT id, SUM(quantity) AS totalCafe1
FROM test_req_concepcion
GROUP BY id
) t1
JOIN
(
SELECT id, SUM(quantity) AS totalCafe2
FROM test_noval
GROUP BY id
) t2
USING (id)

 

That will provide a result with columns for: the ID, the total from the first table, the total from the second table, and the total from both tables.

 

But, if either table may have an id that is not in the other table, those records won't be returned. If one table will absolutely have all the IDs and only the other table may be missing some IDs then that can be resolved by doing a LEFT/RIGHT join as needed. But, if both tables may be missing an ID that is in the other it gets a little more complicated. I'm not willing to do any more until you have clarified

[Taku]

ohhh Thank you very much guys, I know now how to do it tnx tnx

[Taku]

just a question again.. how to inner join if I have more than 2 tables?

[Jessica]

The same way as with 2. 

[Taku]

yes

[Taku]

sorry for disturbing again... I did try to do this.. and it seems not working `.` can someone explain what did I do wrong and how to fix this? :B tnx so much

 

$sql = "
      SELECT t1.id, t1.quantity, t2.quantity, t3.quantity, t4.quantity (t1.quantity+t2.quantity+t3.quantity+t4.quantity) as total
   FROM t1
       INNER JOIN t2
       ON t2.id = t1.id
INNER JOIN t3
ON t3.id = t2.id
INNER JOIN t4
ON t4.id = t3.id
   $qry = mysql_query($sql);
   while($row = mysql_fetch_assoc($qry))
{


   echo $row['total']. '<br/>';
}

[Psycho]

What does "and it seems not working" mean? Is it failing? Are you getting results but not what you expected, or what?

[Psycho]

You never really provided an adequate explanation of your table structure - I thought you needed a GROUP BY. But, if every ID exists in all tables AND there is one record for each ID then use this:

 

SELECT t1.id, t1.quantity AS qty1, t2.quantity AS qty2, t3.quantity AS qty3, t4.quantity AS qty4,
   (t1.quantity + t2.quantity + t3.quantity + t4.quantity) as total
FROM table1 AS t1
INNER JOIN table2 AS t2
 ON t2.id = t1.id
INNER JOIN table3 AS t3
 ON t3.id = t1.id
INNER JOIN table4 AS t4
 ON t4.id = t1.id

Edited February 11, 2013 by Psycho

[Taku]

ohhh tnx psycho, I think I got now the idea.. and if I want to subtract something I will add subtraction in the end? like this?

 

SELECT t1.id, t1.quantity AS qty1, t2.quantity AS qty2, t3.quantity AS qty3, t4.quantity AS qty4, t5.quantity AS qty5
          (t1.quantity + t2.quantity + t3.quantity + t4.quantity - t5.quantity) as total
FROM table1 AS t1
INNER JOIN table2 AS t2
 ON t2.id = t1.id
INNER JOIN table3 AS t3
 ON t3.id = t1.id
INNER JOIN table4 AS t4
 ON t4.id = t1.id
INNER JOIN table5 AS t5
 ON t5.id = t1.id