Re: count distinct support?

Dave Dyer <[email protected]> Thu, 10 Nov 2005 08:17:54 -0800

Newsgroups	gmane.comp.db.mysql.perl
Message-ID	<[email protected]>

At 06:43 AM 11/10/2005, Simon Rees wrote:
>On Thursday 10 November 2005 00:48, Dave Dyer wrote:
>> Current version of dbd-mysql doesn't seem to support COUNT(DISTINCT xx)
>> support, although current versions of mysql do:
>
>It works for me. The only slight difference is I'm using a column alias. e.g:
>
>SELECT COUNT( DISTINCT m.id ) AS count
>FROM mortgage_cases m
>...
>
>Give that a try. I'm using MySql 4.0.18, DBI 1.41 and DBD-mysql 3.0002

No joy here, using mysql 4.0.25 
DBD-mysql 3.0002: up to date.
DBI 1.48: up to date.

The exact query is
SELECT COUNT ( DISTINCT ip) as count FROM hit WHERE client='1' AND latitude>=1000 AND longitude>=1000 AND (date > 1131034482 AND date <= 1131639282)

I've attached the schema


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]

proto.txt (text/plain, 1.5 KB)

-- MySQL dump 9.11
--
-- Host: localhost    Database: maphit
-- ------------------------------------------------------
-- Server version	4.0.25-nt-log

--
-- Table structure for table `city`
--

CREATE TABLE city (
  Occurance double(16,4) default NULL,
  cityid double(16,4) default NULL,
  City char(255) default NULL,
  Lat double(16,4) default NULL,
  Lon double(16,4) default NULL
) TYPE=ISAM PACK_KEYS=1;

--
-- Table structure for table `client`
--

CREATE TABLE client (
  client int(11) NOT NULL default '0',
  name tinytext,
  email tinytext,
  changed timestamp(14) NOT NULL,
  UNIQUE KEY idx1 (client)
) TYPE=ISAM PACK_KEYS=1;

--
-- Table structure for table `facts`
--

CREATE TABLE facts (
  name varchar(32) NOT NULL default '',
  value varchar(64) default NULL,
  type varchar(12) default NULL,
  description varchar(128) default NULL,
  KEY name (name)
) TYPE=ISAM PACK_KEYS=1;

--
-- Table structure for table `hit`
--

CREATE TABLE hit (
  ip char(16) NOT NULL default '',
  date int(11) NOT NULL default '0',
  client int(11) NOT NULL default '0',
  latitude float(10,2) default NULL,
  longitude float(10,2) default NULL,
  certainty float(10,4) default '0.0000',
  KEY date (date),
  KEY ip (ip),
  KEY client (client,date)
) TYPE=ISAM PACK_KEYS=1;

--
-- Table structure for table `ipaddress`
--

CREATE TABLE ipaddress (
  ip char(16) NOT NULL default '',
  latitude float(10,2) default NULL,
  longitude float(10,2) default NULL,
  certainty float(10,4) default '0.0000',
  date int(11) default NULL,
  UNIQUE KEY idx1 (ip)
) TYPE=ISAM PACK_KEYS=1;