开发者

count amount for sub code

tab1

id    code   name
1001   0     palani
1002  1001   shanker
1003  1002   raghu
1004  1003   kabhir
1005  1003   vani
1006  1002   priya

tab2

id  code  name  amount  tax
1   1002  b      100     1
2   1002  j      20      10
3   1003  jk     23      20
4   1004  jk     675     9 
5   1005  o      67       3 
6   1003  u      122      4
7   1003  o      98       1
8   1003  iu     98       1
9   1002  po     4        0.4
10  1005  pl     1        0.1  
12  1005  tf     1        0.1
24  1006  e      23       2.3 

id 1001 see code 1001 corresponding id 1002

id 1002 see code 1002 corresponding id 1003,1006

id 1003 see code 1003 corresponding id 1004,1005

others no need

Result need like this

code   amount   tax
1001   开发者_开发知识库124      11.4
1002   364      28.3
1003   744      12.2

Please send the mysql query for this


GROUP BY is your friend:

select tab1.id, sum (tab2.amount) amount, sum (tab2.tax) tax
from
tab1, tab2
where tab1.code = tab2.id
and tab1.id in (1001, 1002, 1003)
group by tab1.id


This will get what you want from tab2

SELECT code, SUM(amount), SUM(tax) FROM tab GROUP BY code ORDER BY code

I can't see how tab1 fits into what you want


does it work (not tested)

select SUM(t2.amount) AS Amount,SUM(t2.tax) AS totalTax,t2.code FROM 
table2 t2
INNER JOIN table1 t1 ON t1.code=t2.code
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜