0
select 
COUNT(pd.property_id) AS `Beginning Total File Count`,
COUNT(pd.recv_dt) as `average days in inventory`,
AVG(pd.status = 'P') as `average days in pre-marketing`,
AVG(pd.status NOT IN('I','C')) as `average days onMarket`,
AVG(pd.status ='U') as `average days UnderContract`,
SUM(pd.status = 'O') as `Total FilesOccupied Status`,
SUM(pd.status = 'O') / COUNT(pd.property_id) as `percentage of Occupied / 
total file count`
from resnet.property_Details pd

I'm trying to get

  1. Beginning total file count
  2. Average days in inventory
  3. Average days in Pre-Marketing
  4. Average days on market
  5. Average days under contract
  6. Total files in occupied status
  7. Percentage of Occupied / total file count

Not sure if my query is written properly, please help :)

enter image description here

IeeTeY
  • 93
  • 7
  • how can we know the calculations with out any explanation? – Vamsi Prabhala Apr 28 '17 at 23:02
  • What kind of explanation? Was just looking for a logic and query write check. – IeeTeY Apr 28 '17 at 23:04
  • SO is not a query checking website. – Vamsi Prabhala Apr 28 '17 at 23:05
  • AVG and SUM work on numeric values, not logical. As @vkp says, SO isn't a code writing service, or a query checking site. What you are looking for isn't simple, and your code is so far off, it isn't even a starting point. To get help here, you need to show that you've done the research and tried to get it working. Lookup "how to ask a question" on stackoverflow. – Sloan Thrasher Apr 28 '17 at 23:41

1 Answers1

1

Ok as a really wild guess, you're after this request :

select 
COUNT(distinct pd.property_id) AS `Beginning Total File Count`,
COUNT(pd.recv_dt) as `average days in inventory`,
AVG(IF(pd.status = 'P', 1,0)) as `average days in pre-marketing`,
AVG(IF(pd.status NOT IN('I','C'), 1,0)) as `average days onMarket`,
AVG(IF(pd.status ='U', 1,0)) as `average days UnderContract`,
SUM(IF(pd.status = 'O', 1,0)) as `Total FilesOccupied Status`,
SUM(IF(pd.status = 'O', 1,0)) / COUNT(pd.property_id) as `percentage of Occupied / 
total file count`
from resnet.property_Details pd

But it seem strange to use AVG() if you're not using a group by statement... ( it's either just a conditional sum or you need to group by something)

Blag
  • 5,818
  • 2
  • 22
  • 45