RE: SQL help needed

  • From: "David Boyd" <davidb158@xxxxxxxxxxx>
  • To: mwf@xxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 10 Jul 2006 13:33:27 -0400

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 McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2006