I am not able to get this SQL statement to work properly. Anyone's help would be greatly appreciated...
I have 3 tables I am pulling data from, market, properties and images. I want to get a count of all the empty rows from aerial, picture and floor respectively in the image table, grouped by marketname, which is derived by looking up the propid (a field in the images table) in the properties table and then getting the marketname from the corresponding marketid. The statement I have so far returns a count of all the properties that have any image in the images table, which obviously does not work right.
SELECT market.marketname as marketname, COUNT(images.picture) AS picture, COUNT(images.aerial) AS aerial, COUNT(images.floor) AS floor FROM images, properties LEFT JOIN market ON market.marketid=properties.marketid WHERE properties.propid=images.propid GROUP BY market.marketid ORDER BY marketname ASC
I tried
SELECT market.marketname as marketname, COUNT(LENGTH('images.picture')>1) AS picture, COUNT(images.aerial) AS aerial, COUNT(images.floor) AS floor FROM images, properties LEFT JOIN market ON market.marketid=properties.marketid WHERE properties.propid=images.propid GROUP BY market.marketid ORDER BY marketname ASC
to see if the LENGTH >1 would help narrow it down, but it didn't make a bit of difference.
Any ideas would be greatly appreciated.
I have 3 tables I am pulling data from, market, properties and images. I want to get a count of all the empty rows from aerial, picture and floor respectively in the image table, grouped by marketname, which is derived by looking up the propid (a field in the images table) in the properties table and then getting the marketname from the corresponding marketid. The statement I have so far returns a count of all the properties that have any image in the images table, which obviously does not work right.
SELECT market.marketname as marketname, COUNT(images.picture) AS picture, COUNT(images.aerial) AS aerial, COUNT(images.floor) AS floor FROM images, properties LEFT JOIN market ON market.marketid=properties.marketid WHERE properties.propid=images.propid GROUP BY market.marketid ORDER BY marketname ASC
I tried
SELECT market.marketname as marketname, COUNT(LENGTH('images.picture')>1) AS picture, COUNT(images.aerial) AS aerial, COUNT(images.floor) AS floor FROM images, properties LEFT JOIN market ON market.marketid=properties.marketid WHERE properties.propid=images.propid GROUP BY market.marketid ORDER BY marketname ASC
to see if the LENGTH >1 would help narrow it down, but it didn't make a bit of difference.
Any ideas would be greatly appreciated.