[rnty]

Hi

Here is the sample database.

Key_Bunch Entity
----------------------
001 Front Door
001 Back Door
001 Terrace
002 Kitchen
002 Emergency
002 Terrace
003 Front Door
003 Emergency
003 Kitchen

Erro:Not a group by expression

Here is what I am doing

select * from Tab_Key group by Key_Bunch having Entity='Terrace' or Entity='Kitchen'.

I want to select a key bunch based on any two conditions.

I am not being able to pin point the solution.Please help.

Thanks and Regards
Rnty

This post has been edited by rnty: 11 December 2012 - 06:58 AM

[baavgai]

The HAVING clause is for the result of the GROUP BY. e.g. HAVING COUNT(*)>1.

You may just want WHERE. Also, SELECT * simply isn't going to work with a GROUP BY, you have either the fields in the GROUP BY clause or aggregate fields, like SUM and COUNT.

[rnty]

Hi baavgai
Thank you for replying.

I tried this

select key_bunch from Tab_Key group by key_bunch where Entity='Terrace' and Entity='Kitchen'

but I am getting an error(using both where and having).

What I am trying to do is-->Say user wants a key bunch with Terrace and Kitchen keys,he should be allotted 002 keybunch.
I am approaching the problem as
'the query should select all the key_bunches,group them and then search which group has the key for both terrace and kitchen.

I am unable to figure out how to search the group after "group by"

This post has been edited by rnty: 11 December 2012 - 08:33 PM

[baavgai]

Statement order is important. Logic and formatting, also up there.

The statement where Entity='Terrace' and Entity='Kitchen' can never be true. If Entity='Terrace', then it cannot Entity='Kitchen'. You can use OR where Entity='Terrace' or Entity='Kitchen', but in this case, I'd use IN where Entity in ('Terrace', 'Kitchen').

The where always comes before group.

select key_bunch, count(*) as EntityCount
	from Tab_Key 
	where Entity in ('Terrace', 'Kitchen')
	group by key_bunch 

More interesting, and perhaps what you want, would be:

select Entity, count(*) as KeyCount
	from Tab_Key 
	group by Entity

Try using that same where in the above.

[rnty]

After understanding your queries I came up with

select key_bunch from Tab_Key where key_bunch in
(select key_bunch from Tab_Key where entity='Terrace') and entity='Kitchen'

Using this I can select key bunch containing both terrace and kitchen keys.

Thanks baavgai

This post has been edited by rnty: 12 December 2012 - 10:02 PM

[baavgai]

??? Ah, I see. That hurts.

This will run an order of magnitude faster and should give the same results:

select a.key_bunch
   from Tab_Key a
      inner join Tab_Key b
         on a.key_bunch=b.key_bunch
            and b.entity='Terrace'
   where a.entity='Kitchen'

Try to avoid IN for sub queries. It really doesn't scale well and is probably your slowest option.

[rnty]

There is no disagreeing with you.Just a quick question.Would a self join be better?

Thanks

[baavgai]

The thing a database does best is join. Self or otherwise. If both sides of the join have an index, that's about as good as it gets.

However, databases can make odd assumptions. There are edge cases where a non join solution may work better. There are a number of tools where you can test your approach. In Oracle, there is "explain plan".

[rnty]

Thanks.