Select using having clause

mandy4ever · #1 (**permalink**) 03-03-2008

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 · #2 (**permalink**) 03-03-2008

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 · #3 (**permalink**) 03-03-2008

Quote:

Originally Posted by 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.

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 · #4 (**permalink**) 03-05-2008

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

susarlasireesha · #5 (**permalink**) 03-05-2008

Try this query also

Code:

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

Quote:

Originally Posted by 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.

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
GROUP_BY and HAVING clause in SQL	nancyphilips	SQL	5	09-09-2008 12:39 AM
For update clause	babi_geek	Oracle	3	03-19-2008 08:26 AM
return clause in cursor	Barbie	Oracle	4	01-31-2007 05:46 AM
Presence of Clause	nehalshah	MainFrame	1	10-09-2006 04:01 PM
Occurs clause in COBOL	timmy	MainFrame	2	07-29-2006 04:23 PM

Select using having clause

Select using having clause

Similar Threads