Select using having clause

[mandy4ever]

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.

[nandini29]

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

[krishnaindia2007]

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.

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

[debasisdas]

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

[susarlasireesha]

Try this query also

select c_id,cnt from 
(SELECT c_id, count(c_id) cnt FROM customer 
group by c_id 
having c_id = min(c_id)) 
where rownum<=1

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.