SEO

#1 (**permalink**) 04-20-2008, 10:03 AM

Hi,

Here is the query I am trying to achieve and having syntax issues

Select count(distinct name, number) from results.

To replicate the situation use the following SQL

create table results (name varchar(100), number int)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
insert into results values ('test2', 2)

Basically the return of the query should be 2. I can achieve this by
doing following query
select count(*) from
(select distinct [name], [number] from results) a

but I want to do it one query as the later query is a big hit on the
performance.

On a large sample of data the second query takes around 2 seconds.

#2 (**permalink**) 04-20-2008, 10:03 AM

In message <1136478618.777363.213780@f14g2000cwb.googlegroups .com>, Sai
<sbillanuka@gmail.com> writes
>Hi,
>
>Here is the query I am trying to achieve and having syntax issues
>
>Select count(distinct name, number) from results.
>
>To replicate the situation use the following SQL
>
>create table results (name varchar(100), number int)
>insert into results values ('test1', 1)
>insert into results values ('test1', 1)
>insert into results values ('test1', 1)
>insert into results values ('test2', 2)
>insert into results values ('test2', 2)
>insert into results values ('test2', 2)
>
>Basically the return of the query should be 2. I can achieve this by
>doing following query
>select count(*) from
>(select distinct [name], [number] from results) a
>
>but I want to do it one query as the later query is a big hit on the
>performance.
>
>On a large sample of data the second query takes around 2 seconds.
>

If you had a syntax problem the query would not run. You have a
performance issue, and quite possibly a data analysis issue.

What version of Informix? What operating system? What is the schema of
the table, including indexes? What does 'a large sample of data' mean?
Have you done UPDATE STATISTICS for the table after loading the data?

--
Surfer!
Email to: ramwater at uk2 dot net

#3 (**permalink**) 04-20-2008, 10:03 AM

create view stupid_results_view(sTupid) as select name || number from
results ;

select * from stupid_results_view ;

select count(distinct sTupid) from stupid_results_view ;

stupid test11

stupid test11

stupid test11

stupid test22

stupid test22

stupid test22

(count)

2
Table and column names reflect my opinion of the way this is
implemented in Informix. I am pretty sure other databases allow
calculations in the count() function or multiple columns but you have
to work around a syntax limitation in informix. I don't think this will
be really fast unless you use functional indexes and informix can
figure out that is what you are trying to do. PS. I am almost certainly
not handling nulls the way that you would want.

#4 (**permalink**) 04-20-2008, 10:03 AM

http://publib.boulder.ibm.com/infoce...c/sqls1047.htm

You can include expressions in the distinct part of the query.

But it has to be one value that you do the distinct on.

Do I see another feature request for my list?

#5 (**permalink**) 04-20-2008, 10:04 AM

Yes, I am pretty sure this is implemented in other databases and would
be handy.

I just checked my SQL in a nutshell book and it has the following entry
about count distinct:

count distinct counts the occurences of all non-NULL values in the
specified column(s).

The paren "s" clenches it. ;-) someone must allow multiple columns.

#6 (**permalink**) 04-20-2008, 10:04 AM

Heck, Yeah.

I know this is a feature in other databases. A quick check of my book
"SQL in a Nutshell" gives the following definition:

"COUNT DISTINCT counts the occurences of all unique, non-null values in
the specified column(s)"

The paren "s" is a clear indication that you can use 1 or more columns.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Having problem with distinct and count