I'm having trouble getting the result out of my query. I want to fetch the sum and total count of an unit sale in transactions, where the transaction is in a specific zip.
Here are my tables:
TABLE unit_type(
id (Primary key)
unit_name (varchar)
department_id (Foreign key)
)
TABLE transaction(
id (PK)
commission_fix_out (int)
transaction_end_week (int)
property_id (FK)
unit_type_id (FK)
...
)
TABLE property(
id (PK)
property_zip_id (FK)
...
)
My unit_types table has the following records:
+-----+----------------------+----------------+
| id | unit_name | department_id |
+-----+----------------------+----------------+
| 1 | WV construction | 1 |
| 2 | WV resale | 1 |
| 3 | WV rent | 1 |
| 4 | BV industrial sale | 2 |
| 5 | BV industrial rent | 2 |
| ... | ... | ... |
+-----+----------------------+----------------+
Here's how my query looks like:
SELECT SUM(commission_fix_out), COUNT(commission_fix_out), unit_name, ut.id
FROM unit_type as ut
LEFT JOIN transaction as t
ON ut.id = t.unit_type_id
RIGHT JOIN property as p
ON (p.id = t.property_id AND p.property_zip_id = 1459)
WHERE ut.department_id = 1
GROUP BY unit_name
ORDER BY ut.id
which results in:
+------------+-------------+-------------+---------+
| SUM(...) | COUNT(..) | unit_name | ut.id |
+------------+-------------+-------------+---------+
| 40014 | 11 | WV resale | 2 |
| NULL | 0 | WV rent | 3 |
+------------+-------------+-------------+---------+
I was expecting another row with WV construction, but it doesn't show up. Anyone who knows where i am wrong with this one?
I managed to fix my problem. I'd like to share my result with you:
SELECT SUM(commission_fix_out), COUNT(commission_fix_out), unit_name
FROM unit_type ut
LEFT JOIN transaction t
ON (ut.id = t.unit_type_id AND t.property_id IN (SELECT id FROM property p WHERE
property_zip_id = 1459))
WHERE department_id = 1
GROUP BY unit_name
ORDER BY ut.id
Instead of using an extra JOIN, i'd tried using a subquery in my ON-clause which gives my next results:
+-----------+-----------+-------------------+------+
| SUM(..) | COUNT() | unit_name | id |
+-----------+-----------+-------------------+------+
| NULL | 0 | WV construction | 1 |
| 40014 | 11 | WV resale | 2 |
| NULL | 0 | WV rent | 3 |
+-----------+-----------+-------------------+------+
0 comments:
Post a Comment