?
Solved

Distinct count in Informix

Posted on 2005-05-06
12
Medium Priority
?
5,809 Views
Last Modified: 2008-02-07

    I am working on a Informix database and I want to take record count from a Informix table like this, but it raise an error , Pl. help

        select count(distinct column1,column2,column3) from table_name

  I want to count rows in a table on distinct columns (notice I didn't say column).

 Thanks.
Hiranya  
0
Comment
Question by:hiranya
12 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13943961
you can't use Distinct with three columns like that.  What are you trying to count?  You want to count the distinct COMBINATION of the three Columns, is that what you are after?

Although this applies to SQL Server, I think it is SQL Standard - across all databases:

"If you use DISTINCT, the expression must consist of a column name only. It cannot include an arithmetic expression"

AW

0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 13944506
What do you are trying to do?
Like Arthur said, you can't do what you did.

Maybe you need something like:

select column1, count(distinct column1) from table_name
union
select column2, count(distinct column2) from table_name
union
select column3, count(distinct column3) from table_name

But this should work only if that 3 columns have the same type
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13946066
why not:

select column1,column2,column3,count(*) from table_name
group by column1,column2,column3;

This will work in some DBs...don't know about Informix (haven't used it since 2000);

select count(*) from table_name
group by column1,column2,column3;

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:hiranya
ID: 13957020


Thank u for ur replies. I just want to  count  the distinct combination of the three Columns. I can use pipelines to cancatenate 3 columns together and take the count. but its not working in informix .
   Like this
               SELECT count(distinct column1||column2||column3) from table_name  

 Thanks
  Hiranya
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13959838
OK.  How about this?

select count(*)
from (select distinct column1, column2, column3 from table_name);
0
 

Author Comment

by:hiranya
ID: 13965108

I tried this. but this method is also not working in Informix. I don't know why.........
We can use this with Oracle.

Thanks
Hiranya
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13965565
OK, here's a few more ideas:

select count(distinct data)
from (select column1||column2||column3 as data from table_name);

select count(*)
from
(select column1, column2, column3 from table_name group by column1, column2, column3);
0
 

Author Comment

by:hiranya
ID: 13965770
Dear jrb1

Thank u very much for ur reply.
thing is we can put a select statement with a FROM clause in Informix. (like we r doing with Oracle).

Regards
Hiranya
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13967965
was that CAN or CAN'T?  Are you OK then?
0
 

Author Comment

by:hiranya
ID: 13974680

Sorry its a mistake.
we can not  put a select statement with a FROM clause in Informix


Thanks
Hiranya
0
 
LVL 25

Accepted Solution

by:
jrb1 earned 750 total points
ID: 13974854
OK.  That explains another post I found from someone else..their solution was to create a temp table, load the distinct values, and then count on the temp table.  Not a great solution in my opinion.  Can you just create a view with "select distinct column1, coumn2, column3...." and then count from the view?
0
 

Expert Comment

by:rwamuhire
ID: 21231132
Well, given I have a fairly good understanding on what you are trying to achieve, here is what you could try to do:

select distinct dismo_id, dismo_val
from dbmitd

assuming that if you did not use distinct, you would return more of the same dismo_id value. I hope this helps.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question