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.
Re: Select using having clause
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
Re: Select using having clause
[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
Re: Select using having clause
You have wrongly specified the order of execution of the clauses.
Re: Select using having clause
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]