select count of rows minus sum of another field

Vote count:

0

i have three table named agency, works and purchases. and there is a Foreign Key from agency in purchases and works. every agency work for us and do some works that the works are in works table and every agency will give one point for each works that they do. for example an agency does 200 works for us, so it has 200 point.

each agency can buy our product by its points. in purchases table there is a field named price, if an agency buy a product it will insert into purchases table. i need a SQL query that give me count of works of each Agency minus sum of its purchases!

Like: select count(works.WID)-SUM(purchases.price) where agency.aid='1'

thank you.

asked 1 min ago

user3359388

3