#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171

    COUNT, GROUP BY query returns wrong results


    Hi;

    I need to count how many ads exist in each suburb. Currently my SQL returns wrong results. Please help me fix my query. Thank you

    sql Code:
     SELECT COUNT(ad_have.id) AS COUNT,
           place_name
    FROM   au_postcodes
           INNER JOIN ad_have
                   ON au_postcodes.id = ad_have.suburb
    WHERE  ad_have.active = 'y'
           AND ad_have.active_admin = 'y'
    GROUP  BY place_name
    ORDER  BY COUNT DESC




    ad_have
    sql Code:
    CREATE TABLE `ad_have` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `emailed_to_wants` INT(11) NOT NULL DEFAULT '0',
     `place_name2` VARCHAR(255) NOT NULL,
     `member_ID` INT(11) NOT NULL,
     `date_added` DATE NOT NULL,
     `comments` text NOT NULL,
     `weekly_rent` VARCHAR(255) NOT NULL,
     `sex` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `smoke` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `available_date` DATE NOT NULL,
     `suburb` VARCHAR(255) NOT NULL,
     `building_type` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `pet` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `active` VARCHAR(255) CHARACTER SET latin1 NOT NULL DEFAULT 'y',
     `title` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `views` INT(11) NOT NULL DEFAULT '0',
     `active_admin` VARCHAR(255) CHARACTER SET latin1 NOT NULL DEFAULT 'n',
     `security_bond` VARCHAR(255) NOT NULL,
     `furnished` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `washer_dryer` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `tv` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `carpark` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `internet` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `own_bathroom` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `aircondition` VARCHAR(255) CHARACTER SET latin1 NOT NULL,
     `address` text CHARACTER SET latin1 NOT NULL,
     `age_max` INT(11) NOT NULL,
     `age_min` INT(11) NOT NULL,
     `default_photo` INT(11) NOT NULL,
     `rank` INT(11) NOT NULL DEFAULT '0',
     `city` INT(11) NOT NULL,
     `d_photo` INT(11) NOT NULL,
     `sent_in_newsletter_date` DATE NOT NULL,
     `sent_in_newsletter` VARCHAR(2) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `member_ID` (`member_ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=42052 DEFAULT CHARSET=utf8


    sql Code:
    CREATE TABLE `au_postcodes` (
     `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
     `postcode` VARCHAR(10) NOT NULL,
     `place_name` VARCHAR(180) NOT NULL,
     `admin_name2` VARCHAR(100) DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=10611 DEFAULT CHARSET=utf8
    Last edited by English Breakfast Tea; December 17th, 2013 at 08:51 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    your INNER join is based on matching an INTEGER to a VARCHAR(255) -- that doesn't seem right

    also, you neglected to mention exactly how the results are wrong -- count too high? count too low?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    Originally Posted by r937
    your INNER join is based on matching an INTEGER to a VARCHAR(255) -- that doesn't seem right

    also, you neglected to mention exactly how the results are wrong -- count too high? count too low?
    Hi Rudy;

    Changed it to int. Still shows too few results. Instead of 244, it shows 15.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    well, i can't see your data from here, you'll have to do it

    inspect the values in both columns -- i'm sure you'll find anomalies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,689
    Rep Power
    171
    Originally Posted by r937
    well, i can't see your data from here, you'll have to do it

    inspect the values in both columns -- i'm sure you'll find anomalies
    Can you confirm my JOIN and GROUP BY are correct?

    Thank you.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by English Breakfast Tea
    Can you confirm my JOIN and GROUP BY are correct?
    your GROUP BY is fine

    i can't confirm the join, though, because like i said, there could be garbage in the columns you're joining on, and i can't verify the data values

    you can actually join two tables on any pair of columns you wish -- whether any one pair makes sense, though, is another matter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo