Gossamer Forum
Quote Reply
Select Statement
Hi All,

I'm trying to turn this...

select DISTINCT(Email) from lsql_Users WHERE ReceiveMail = 'Yes';

into "something" like this - have I done the DISTINCT bit correctly??

my $cond = new GT::SQL::Condition;
$cond->add(ReceiveMail => 'Yes');
$sth = $DB->table('Users')->select($cond, ['DISTINCT(Email)']);

Thanks

Regan.
Quote Reply
Re: [ryel01] Select Statement In reply to
This should work ok;

Code:
my $cond = new GT::SQL::Condition->new('ReceiveMail','=','Yes');
$sth = $DB->table('Users')->select(['DISTINCT(Email)'], $cond);

...or according to the new system (which I don't really get), it should be something like;

Code:
my $cond = new GT::SQL::Condition->new('ReceiveMail' => '=' => 'Yes');
$sth = $DB->table('Users')->select(['DISTINCT(Email)'], $cond);

... or something like that Pirate

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Select Statement In reply to
Hi Andy

Do you know how to get a count from a select statement using the GT code? I've tried a number of different ways but can't figure it out.

What I want to end up with is a $count variable that has a number in it by adapting the following statement.

SELECT count( DISTINCT(Email) ) FROM lsql_Users WHERE ReceiveMail = 'Yes'


regan
Quote Reply
Re: [ryel01] Select Statement In reply to
Hi. It should work with;

my $count = $DB->table('Users')->count( { ReceiveMail => 'Yes' } );

Not sure how to get DISTINCT working in it though :(

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Select Statement In reply to
me either. Unimpressed

hopefully someone from GT might read this and shed some light on it.

thanks for your help!

Regan.
Quote Reply
Re: [ryel01] Select Statement In reply to
How about this:

$total = $DB->table('Users')->select(['DISTINCT(Email)'], {ReceiveMail=>'Yes'})->rows;
Quote Reply
Re: [afinlr] Select Statement In reply to
In Reply To:
How about this:

$total = $DB->table('Users')->select(['DISTINCT(Email)'], {ReceiveMail=>'Yes'})->rows;


Thanks Laura - that's done it!

regan
Quote Reply
Re: [ryel01] Select Statement In reply to
Hi,

A bit better would be:

my ($total) = $DB->table('Users')->select(['COUNT(DISTINCT(Email))'], {ReceiveMail=>'Yes'})->fetchrow_array;

but you are getting into non-portable SQL here. I don't believe this works in mysql 3.23 (or maybe 3.22). This will be a bit better as the database server is not fetching all the rows, but rather it's doing the counting.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Select Statement In reply to
Thanks for that Alex - I've been finding it difficult using counts of distinct values in large tables because they are very slow and I couldn't work out why. Hopefully this will speed things up.

Last edited by:

afinlr: Jul 5, 2004, 3:50 PM