count distinct support?

Current version of dbd-mysql doesn't seem to support COUNT(DISTINCT xx)
support, although current versions of mysql do:

mysql> select count(distinct ip) from hit where client=1;
+--------------------+
| count(distinct ip) |
+--------------------+
| 28409 |
+--------------------+
1 row in set (0.39 sec)


DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right
syntax to use near '( DISTINCT ip) FROM hit WHERE client='1'



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: count distinct support?

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

cheers Simon

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Simon Rees | tech-lists@zodiac2000.co.uk |
ORA-03113: end-of-file on communication channel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: count distinct support?

--=====================_34605984==_
Content-Type: text/plain; charset="us-ascii"

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

--=====================_34605984==_
Content-Type: text/plain; charset="us-ascii"
Content-Disposition: attachment; filename="proto.txt"

-- 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;



--=====================_34605984==_
Content-Type: text/plain; charset=us-ascii


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
--=====================_34605984==_--

Re: count distinct support?

On Thu, 10 Nov 2005 08:17:54 -0800, Dave Dyer wrote
> 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
try (I think the after count is the problem) =)

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




--
[ Toth ]
(Warning: This message may cause you to understand something)


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: count distinct support?

>
>> I've attached the schema
>try (I think the after count is the problem) =)

Yes, that does it. I'll leave it to you to resolve the implied
incompatability with the mysql command line tool (which accepts
the space). I haven't tried the C api.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org