RE: SQL help needed
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <kennaim@xxxxxxxxx>, <davidb158@xxxxxxxxxxx>, <mwf@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Jul 2006 13:02:44 -0500
Ignore my last email as I misread yours as I didn't consider that a long sql
statement. The only other way would be to encompass that logic in a function
but you would be adding a lot of overhead into the query due the context
switches even if you made it deterministic.
Ken Naim
-----Original Message-----
From: Ken Naim [mailto:kennaim@xxxxxxxxx]
Sent: Monday, July 10, 2006 12:59 PM
To: 'davidb158@xxxxxxxxxxx'; 'mwf@xxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: SQL help needed
Just add one in.
SELECT name, SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
WHEN GROUPS LIKE 'GROUP B%' THEN 2
WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END)) group_value
FROM test
GROUP BY name
HAVING SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
WHEN GROUPS LIKE 'GROUP B%' THEN 2
WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END))
= (SELECT SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
WHEN GROUPS LIKE 'GROUP B%' THEN 2
WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END))
FROM test WHERE name ='Marry');
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Boyd
Sent: Monday, July 10, 2006 12:33 PM
To: mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: SQL help needed
Hi Mark,
Excellent. That's the answer I wanted. Thank you so much.
Do you know if there is any way that I can use an alias for sum clause?
Otherwise the query is long as following:
SELECT name, SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
WHEN GROUPS LIKE 'GROUP B%' THEN 2
WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END)) FROM test
GROUP BY name
HAVING SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
WHEN GROUPS LIKE 'GROUP B%' THEN 2
WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END))
= (SELECT SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
WHEN GROUPS LIKE 'GROUP B%' THEN 2
WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END))
FROM test WHERE name ='Marry');
Dave
>From: "Mark W. Farnham" <mwf@xxxxxxxx>
>To: <davidb158@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
>Subject: RE: SQL help needed
>Date: Mon, 10 Jul 2006 12:09:31 -0400
>
>name group group_value
>M A 1
>M B 2
>M dev 4
>D etc.......
>
>
>select name, sum(group_value) from test
> group by name
> having group_value = (select sum(group_value) from test where name =
>'M'));
>
>should work (off of the top of my head and my typing is not tested.)
>
>Notice those group_values are disjoint powers of two. You could craft a
>decode on your existing data to do the same thing. If you don't enforce
>uniqueness on name-group tuples this could generate false answers, and
>you'd
>have to make the statement more complicated.
>
>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
>Behalf Of David Boyd
>Sent: Monday, July 10, 2006 11:18 AM
>To: oracle-l@xxxxxxxxxxxxx
>Subject: SQL help needed
>
>Hi List,
>
>I was wondering if a SQL statement is possible for following query.
>
>The name of the table is test. It has following data:
>
>name group
>Marry group A 1-1
>Marry group B 1-2
>Marry group dev 1-1
>Ann group A 2-3
>Dave group A 2-3
>Dave group B 4-1
>Dave group dev 3-2
>
>I want to find out the names that are in the exact same groups as 'Marry'.
>The number in the group has to be ignored. For the above data, the query
>should return 'Dave' only since both Marry and Dave are in group A, group
>B,
>and group dev.
>
>Thanks for any input advance.
>
>Dave
>
>_________________________________________________________________
>Express yourself instantly with MSN Messenger! Download today - it's FREE!
>http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfeeR
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
