Divide a value in one table by a value in another table?

[RJP1]

Hi guys,

 

I was wondering the best way to do this...

 

I'd like to output the result of number_x divided by number_y along with the data that is in Table 1 in ASC order of the division result. Is that easily doable?

 

Table 1:

id | userid | name | email | number_x

 

Table 2

id | userid | number_y

 

Thanks for any help guys, would be very useful!

 

RJP

[pets2soul]

I would say...LEFT JOIN both tables...like this:

 

SELECT table1.number_x, table2.number_y FROM table1 LEFT JOIN table2 ON table1.userid = table2.userid ORDER BY table1.id ASC

[RJP1]

Hmm, I think I got my question wrong last night...

 

What I need it to do is this:

 

Table 1:

id | userid | name | email | number_x

 

Table 2

id | userid

 

Count the number of records in table2 that have the same userid as table1, then divide number_x by this count and then output all data from table 1 (with the calculation) in ASC order...

 

That's was what I meant because the number_y doesn't exist as such, it is actually the count of rows in the second table.

 

Cheers, RJP

[kickstart]

Hi

 

This should do it:-

 

SELECT id, userid, name, email, number_x, number_x / bCount AS CalcField

FROM (SELECT a.id, a.userid, a.name, a.email, a.number_x, COUNT(b.id) AS bCount

FROM Table1 a

INNER JOIN Table2 b

ON a.userid = b.userid

GROUP BY a.id, a.userid, a.name, a.email, a.number_x) fred

ORDER BY CalcField

 

You might be able to do it in one go without the subselect but not certain.

 

All the best

 

Keith

[RJP1]

Thanks kickstart that seems to work! By the way, what is the "fred" about? Cheers!

 

RJP1

[kickstart]

Hi

 

When joining with a subselect you need to give it an alias name even if you don't use that alias name.

 

I just called it fred to give it a name.

 

All the best

 

Keith

[RJP1]

Nice, thanks a lot Keith, this has helped me to learn what can be done no end! Cheers!