8 Replies - 5562 Views - Last Post: 23 December 2012 - 03:39 AM

#1 rnty  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 145
  • Joined: 14-August 12

Select a group based on a condition

Posted 11 December 2012 - 06:56 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Select a group based on a condition

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,636
  • Joined: 16-October 07

Re: Select a group based on a condition

Posted 11 December 2012 - 07:01 AM

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.
Was This Post Helpful? 1
  • +
  • -

#3 rnty  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 145
  • Joined: 14-August 12

Re: Select a group based on a condition

Posted 11 December 2012 - 08:31 PM

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

Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,636
  • Joined: 16-October 07

Re: Select a group based on a condition

Posted 12 December 2012 - 06:02 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 rnty  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 145
  • Joined: 14-August 12

Re: Select a group based on a condition

Posted 12 December 2012 - 10:01 PM

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

Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,636
  • Joined: 16-October 07

Re: Select a group based on a condition

Posted 13 December 2012 - 09:56 AM

??? 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.
Was This Post Helpful? 0
  • +
  • -

#7 rnty  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 145
  • Joined: 14-August 12

Re: Select a group based on a condition

Posted 20 December 2012 - 09:10 PM

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

Thanks
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,636
  • Joined: 16-October 07

Re: Select a group based on a condition

Posted 21 December 2012 - 04:04 PM

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".
Was This Post Helpful? 1
  • +
  • -

#9 rnty  Icon User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 145
  • Joined: 14-August 12

Re: Select a group based on a condition

Posted 23 December 2012 - 03:39 AM

Thanks.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1