Ive got 5 tables that I'd like to LEFT JOIN together. Tables are : visitors, offers, contracts1, contracts2 and contracts3.
QUERY:
SELECT
count(DISTINCT visitors.ID) as visitors,
sum(
CASE
WHEN offers.ACTIVE = 1 THEN 1
ELSE 0
END) as offers,
count(contracts1.ID) as contracts1, sum(contracts1.PRICE) as sum_contracts1,
count(contracts2.ID) contracts2,
sum(
CASE
WHEN contracts2.PAYMENT = 'YEARLY' THEN contracts2.PRICE
WHEN contracts2.PAYMENT = 'TWICE' THEN contracts2.PRICE*2
ELSE contracts2.PRICE*4
END) as sum_contracts2,
count(contracts3.ID) as contracts3, sum(contracts3.PRICE) as sum_contracts3
FROM visitors
LEFT JOIN offersON offers.VISITOR_ID = visitors.ID AND (offers.IP > 100 OR offers.IP < 0)
LEFT JOIN contracts1 ON
(offers.ID = contracts1.ID_OFFER)
LEFT JOIN contracts2 ON
(offers.ID = contracts2.ID_OFFER)
LEFT JOIN contracts3 ON
(offers.ID = contracts3.ID_OFFER)
WHERE visitors.TIME >= '2017-01-01 00:00:00' AND visitors.TIME <= '2017-05-25 23:59:59'
Problem here is, that contracts1, contracts2 and contracts3 have no common column in order to be joined together. So instead of 20 rows for contracts1, 30 for contracs2 and 50 for contracts3 i get all the combination for all of them. Because they are joined based on visitors and offers tables. Simple GROUP BY in the end of the query would normally solve the problem, but if I use GROUP BY in the END for one of those tables (or all of them), it will create MULTIPLE ROWS instead of 1 that I want. And also it would erase all the other result for the part where i count visitors by ID and also offers by ID ... I can use DISTINCT on count() parts of the SELECT but not one the sum() because PRICE of the contracts may be same even though IDs are not (you know like for example 2 chocolates are 2 rows with different IDs but same PRICE for 10 dollars each).
So my question is:
Is there any way to SUM only those PRICES of contracts1, contracts2 and contracts3, that have DISTINCT ID and though get rid of adding up the duplicates? And is it possible without creating VIEW?
I also tried GROUP BY inside of the LEFT JOIN but again when i LEFT JOINED all 3 contracts tables together, even though i GROUPED them before I ended up with duplicates.
Example of expected result:
In that time horizon which I stated above I would expect: 80 visitors that have 35 offers and 5 contracts1 with sum of 1000 euros, 12 contracts2 with sum of 686 euros and 3 contracts3 with sum of 12 euros. It is ONE ROW with 8 columns of data.
Instead of expected result I got: 80 visitors, 35 offers, 180 contracts1 (sum is also bad), 180 contracts2 (sum is also bad), 180 contracts3 (sum is also bad).