Thread: SQL Question

SQL Question

From
Matthew
Date:
In 7.0.3 I want to do:

select count( distinct browser, ssn) from applicant_sessions where browser
like '%5.5%';

But I get this error:

ERROR:  Aggregate functions may only have one parameter

In 7.1 I can do:
select count(*) from (select distinct browser, ssn from applicant_sessions
where browser like '%5.5%') as distinctbrow;

to get the desired result, but I can't do that in 7.0.x.  Any suggestions?
Am I doing anything wrong with my sql syntax.  I'm working around it right
now by doing a pg_numrows from PHP after I do the first select, but I would
like to get the count directly as it will be faster.

Thanks,

Matt

Re: SQL Question

From
"Mitch Vincent"
Date:
> select count( distinct browser, ssn) from applicant_sessions where browser
> like '%5.5%';
>
> But I get this error:
>
> ERROR:  Aggregate functions may only have one parameter

To be expected for count, I think..

> In 7.1 I can do:
> select count(*) from (select distinct browser, ssn from applicant_sessions
> where browser like '%5.5%') as distinctbrow;
>
> to get the desired result, but I can't do that in 7.0.x.  Any suggestions?
> Am I doing anything wrong with my sql syntax.  I'm working around it right
> now by doing a pg_numrows from PHP after I do the first select, but I
would
> like to get the count directly as it will be faster.

Are you not doing the query SELECT * FROM applicant_sessions WHERE browser
LIKE '%5.5%'  ? If you're not then yes, selecting just the count will be
faster but if you're already doing the query then why not just see how many
rows are returned by it.. It's one less step if you have to run the query
anyway..

> select count(*) from (select distinct browser, ssn from applicant_sessions
> where browser like '%5.5%') as distinctbrow;

That would be slower than just the inner SELECT by itself, so you're not
gaining anything even by doing that in 7.1.. You're still executing that
sub-select and doing processing against it.. *shrug*

Just some thoughts.. Good luck..

-Mitch