CODEWITHSUNDEEP
mysqlselectsubquery
phizzy
phizzy

Reputation: 956

How to count the results of a where clause in order to calculate a proportion a MYSQL select clause?

I'm starting out with this query, which gives me back 8 records with a "claimed" status. I'm looking to see if any of the addresses in the invites-from-address column are different from that in the moves-from-address column :

SELECT i.id, i.company_id, i.status,  
  ia_f.base_street as "invites-from-address", a_f.base_street as "moves-from-address", 
  ia_t.base_street as "invites-to-address", a_t.base_street as "moves-to-address", i.`mover_first_name`, 
  i.mover_last_name, i.`to_address_id`

FROM invites i
JOIN moves m ON i.id = m.`claimed_invite_id`
JOIN `invite_addresses` ia_f ON ia_f.id = i.`from_address_id`
JOIN addresses a_f ON a_f.id = m.from_address_id

JOIN `invite_addresses` ia_t ON ia_t.id = i.to_address_id
JOIN addresses a_t ON a_t.id = m.to_address_id

WHERE i.`company_id` = 1040345
GROUP BY id

What I'm trying to do in this query below is to create an average_discrepancy column on the fly that shows the proportion of addresses that differ between invites-from-address and moves-from-address. I was able to successfully check for address discrepancies by using a WHERE clause that checks that ia_f.base_street is not equal to a_f.base_street (which are aliased to the columns invites-from-address and moves-from-address respectively) but when I put this WHERE clause inside the count function in my SELECT cause it doesn't work. Is it because I can't place a WHERE clause inside a SELECT or a count function or both? And is there also a problem with trying to divide the results of two calls to the count function in my SELECT clause ?

SELECT i.id, i.company_id, i.status, 
  count(WHERE ia_f.base_street != a_f.base_street)/count(i.status="claimed") as "average_discrepancy", 
  ia_f.base_street as "invites-from-address", a_f.base_street as "moves-from-address", 
  ia_t.base_street as "invites-to-address", a_t.base_street as "moves-to-address",
  i.`mover_first_name`, 
  i.mover_last_name, i.`to_address_id`

FROM invites i
JOIN moves m ON i.id = m.`claimed_invite_id`
JOIN `invite_addresses` ia_f ON ia_f.id = i.`from_address_id`
JOIN addresses a_f ON a_f.id = m.from_address_id

JOIN `invite_addresses` ia_t ON ia_t.id = i.to_address_id
JOIN addresses a_t ON a_t.id = m.to_address_id

WHERE i.`company_id` = 1040345

AND i.status = "claimed"

Upvotes: 0

Views: 21

Answers (1)

ghg565
ghg565

Reputation: 422

You need to put this into a SUM instead of a COUNT. Something like this would do the trick:

 SELECT i.id, i.company_id, i.status, 
  SUM(CASE WHEN ia_f.base_street != a_f.base_street THEN 1 ELSE 0 END)/ SUM(CASE WHEN i.status='claimed' THEN 1 ELSE 0 END) as 'average_discrepancy', 
  ia_f.base_street as 'invites-from-address', 
  a_f.base_street as 'moves-from-address', 
  ia_t.base_street as 'invites-to-address', 
  a_t.base_street as 'moves-to-address',
  i.mover_first_name, 
  i.mover_last_name, 
  i.to_address_id

FROM invites i
JOIN moves m ON i.id = m.claimed_invite_id
JOIN invite_addresses ia_f ON ia_f.id = i.from_address_id
JOIN addresses a_f ON a_f.id = m.from_address_id

JOIN invite_addresses ia_t ON ia_t.id = i.to_address_id
JOIN addresses a_t ON a_t.id = m.to_address_id

WHERE i.company_id = 1040345

AND i.status = 'claimed'

Upvotes: 1

Related Questions