Select using having clause

Hi,

I'm trying to execute a simple query that returns the minimum value from a column and the count of it:

SELECT c_id, count(c_id) FROM customer
having c_id = min(c_id)
group by c_id

But instead of returning a single row (the minimum value) this returns ALL the c_id's in this table along with their count. can someone please tell me where I'm going wrong.

Thanks in advance.

hi

you are using wrong order in query.
first write group by
second having clause

after that run the query you will get right answer.

nandini

[QUOTE=mandy4ever;27059]Hi,

I'm trying to execute a simple query that returns the minimum value from a column and the count of it:

SELECT c_id, count(c_id) FROM customer
having c_id = min(c_id)
group by c_id

But instead of returning a single row (the minimum value) this returns ALL the c_id's in this table along with their count. can someone please tell me where I'm going wrong.

Thanks in advance.[/QUOTE]


Use the following query

select c_id,count(c_id) from customer
where c_id = (select min(c_id) from customer)
group by c_id

The mistake in your query is It executes group by first then having clause.
Group by C_id identifies distinct C_id's from your table.
Then having clause finds minimum C_id in that group which is c_id itself.
If c_id is a primary key then Your query will display all c_id's in your table and its count as 1.

Regards
Krishna

You have wrongly specified the order of execution of the clauses.

Try this query also
[CODE]select c_id,cnt from <br>
(SELECT c_id, count(c_id) cnt FROM customer <br>
group by c_id <br>
having c_id = min(c_id)) <br>
where rownum<=1 <br>[/CODE]

[QUOTE=mandy4ever;27059]Hi,

I'm trying to execute a simple query that returns the minimum value from a column and the count of it:

SELECT c_id, count(c_id) FROM customer
having c_id = min(c_id)
group by c_id

But instead of returning a single row (the minimum value) this returns ALL the c_id's in this table along with their count. can someone please tell me where I'm going wrong.

Thanks in advance.[/QUOTE]