mysql - join two table and sum count record -

mysql - join two table and sum count record -

i need bring together 2 tables, tables:

table 1

user ip amount time user1 10.12.130.14 5000 1409007653 user2 10.12.130.13 2000 1309007653 user3 10.12.130.12 4000 1209007653 user4 10.12.130.11 6000 1109007653 ...

table 2

ref ip time ref1 10.12.130.14 1209007653 ref1 10.12.130.13 1109007653 ref1 10.12.130.12 1309007653 ref2 10.12.130.11 1409007653 ...

i need result: ( foreach ref )

ref allcount todaycont allsum todaysum ref1 3 1 11000 5000 ref2 1 0 6000 0 ...

the code wrote it's not complete:

$query = mysql_query("select table2.ref, table1.user table2 inner bring together table1 on table2.ip = table1.ip ") or die(mysql_error());

untested, interpretation of question, should work:

select a.ref, count(*) allcount, case when date_format(a.time,'%m-%y') = date_format(now(),'%m-%y') count(*) end todaycount, sum(a.amount), case when date_format(a.time,'%m-%y') = date_format(now(),'%m-%y') sum(a.amount) end todaysum table1 bring together table2 b on a.ip = b.ip grouping b.ref

the biggest part i'm unsure of date/time segments in case statements. that's due unfamiliarity date/time format you're using in table, should work if tweaked datatype.

mysql table join record min