0

I have a table in my database whose structure is like this

sell_id(int) | car_id(int) | worth(int) | date(date) | selled(boolean)

Selled is a wrong 'sold'.

My table is like this

------------------------------
50 |50 |2405 | "2012-07-16" | false
51 |51 |9300 | "2014-10-07" | false
52 |52 |5963 | "2014-11-01" | false
53 |53 |2036 | "2014-09-19" | false
54 |54 |4770 | "2014-01-26" | false
55 |55 |11915| "2010-08-30" | true

Anyway, sell_id is the primary key of this table and a foreign key to a "mother" table. car_id refers to another table as well. Worth is the amount of money a seller sold or bought a car. Date is just when and selled is a boolean to specify whether the seller sold or bought the car. If it is true then he did sell it. Otherwise he bought it.

Now i want to find the user who has made the most value out of selling/buying. This means that he must be the one to have the selled-bought.But i cant figure out the right query.

this query gives me the values of only selling.

select sell_id, sum(worth), selled 
from sellers 
where(selled=true) 
group by sell_id, selled

Some desired result for example would be:

sell_id | total_profits(max)
---------------------------
  51    |     2000

where total_profits would be the maximum worth(where selled=true)-worth(where selled=false)

Any ideas would be really helpfull. Thanks :)

MonkeyDkon
  • 54
  • 11

2 Answers2

0

If i understand your question correctly, you might calculate separately and then join for the final calculation. The first version gives you three queries. The first puts the sells into a table, the second puts the buys, and the third joins them together to give you the ability to do the math. 

select sell_id, sum(worth), selled 
into #sell
from sellers 
where(selled=true) 
group by sell_id, selled

select sell_id, sum(worth), selled 
into #buy
from sellers 
where(selled=false) 
group by sell_id, selled

select a.sell_id, sum(a.worth-b.worth) as 'Total'
from #sell a
join #buy b on a.sell_id = b.sell_id
group by a.sell_id

You may need to adjust the join, if a straight join excludes expected records. As you note, temporary tables don't work for you, maybe try this. 

select sell_id, sum(sum(worth)-b.worth), selled 
from sellers a
join (select sell_id, sum(worth) as 'worth' from sellers where(selled=false) 
group by sell_id) b on a.sell_id = b.sell_id
where(selled=true) 
group by sell_id, selled

I'm not somewhere I can adequately test, may need to tweak a little, but this is the concept. You're doing the calculation on the buys in the join and then subtracting them from the sum of the sells, conceptually, regardless of the method.

JenInCode
  • 250
  • 2
  • 8
  • in TSql it will create a temporary table to store the value. you can remove it, but you'll have to drop that table, as it will create it for you. If that doesnt work there is a sub-query method i can show you that might – JenInCode May 18 '18 at 18:32
  • i would appreciate it if you could – MonkeyDkon May 18 '18 at 18:37
0

i finally found the answer to my own question. the correct query is :

select sell_id, (sum(worth) - (select sum(worth) 
   from sellers where selled=false group by sell_id 
       order by sum limit 1)) as final from sellers 
             where selled=true   
            group by sell_id order by final desc limit 1
MonkeyDkon
  • 54
  • 11