Saturday, 24 August 2013

MYSQL Query - grabbing all the records that meet the criteria 3 or more times

MYSQL Query - grabbing all the records that meet the criteria 3 or more times

Not sure how to do this but I am trying to grab all the parcels from a
MYSQL DB that have had multiple years of delinquencies. Right now I can
count how many delinquencies I have for the years given using the
following query:
SELECT parcel, COUNT(), due FROM deliquent_property WHERE year IN ('2013',
'2012', '2011', '2010', '2009') AND
CAST(replace(replace(ifnull(due,0),',',''),'$','') AS decimal(10,2)) > 0
GROUP BY parcel HAVING COUNT() > 2;
So I get something like this
'XXX-XXX-XXX1' '3' '167.00'
'XXX-XXX-XXX2' '4' '190'
Where the amount is the last record for that parcel in the DB. The problem
is what I really want is a list of parcels with each years overdue amount
shown, something like this (skipping those that do not have a least 3
delinquencies):
'XXX-XXX-XXX1' '2013' '1267.78'
'XXX-XXX-XXX1' '2012' '1000.38'
'XXX-XXX-XXX1' '2011' '167.00'
'XXX-XXX-XXX2' '2013' '1000.00'
'XXX-XXX-XXX2' '2012' '500.00'
'XXX-XXX-XXX2' '2011' '100.00'
'XXX-XXX-XXX2' '2010' '190.00'
I am half way there but I am lost on getting this last part.
Thanks

No comments:

Post a Comment