Home » SQL & PL/SQL » SQL & PL/SQL » sql problem (merged)
sql problem (merged) [message #376420] Wed, 17 December 2008 05:09 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member

The below is the result of a group by statement:

ACTION LOCATION USERID RECD
DELETED 01 P1 50
DELETED 02 P2 10
DELETED 03 P3 10
INSERTED 01 P1 20
INSERTED 02 P2 20
INSERTED 03 P3 10
UPDATED 02 P2 20


I have filtered based on acrion name='DELETED'
select ACTION,  LOCATION, USERID, sum( RECD)
from table
group by ACTION,  LOCATION, USERID


Result2: ( query result)

ACTION LOCATION USERID RECD
DELETED 01 P1 40 --------c1
DELETED 02 P2 50 -------c2
DELETED 03 P3 50


Now I need to filter the records for case2

where percentage of c1 /total count of action for the user




sql problem [message #376425 is a reply to message #376420] Wed, 17 December 2008 05:22 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
SELECT ACTION,USERID, LOCATION,  SUM(RECD) AS RECD FROM TABLE1
GROUP BY ACTION,USERID LOCATION 


The below is the result of a group by statement:

ACTION LOCATION USERID RECD
DELETED 01 P1 10
DELETED 02 P2 10
DELETED 03 P3 30
INSERTED 01 P1 10
INSERTED 02 P2 10
INSERTED 03 P3 10
UPDATED 02 P2 20


I have filtered based on acTion name='DELETED'
SELECT ACTION,USERID, LOCATION,  SUM(RECD) AS RECD FROM TABLE1
WHERE ACTION='DELETED'
GROUP BY ACTION,USERID LOCATION


RESULT:

ACTION LOCATION USERID RECD
DELETED 01 P1 10
DELETED 02 P2 10
DELETED 03 P3 30


Now problem is filter the records for RESULT

Give me the records which is

above 50% OF ( RECD/SUM OF ALL ACTION FOR A USER)

[Updated on: Wed, 17 December 2008 05:25]

Report message to a moderator

Re: sql problem [message #376426 is a reply to message #376425] Wed, 17 December 2008 05:49 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
This is what I tried..

SELECT * FROM TEST_DUMMY1;

SELECT ACTION,LOCATION,USERID,SUM(CNT1) RECD 
FROM TEST_DUMMY1 GROUP BY ACTION,LOCATION,USERID;

SELECT user_id,SUM(CNT1) FROM TEST_DUMMY1 GROUP BY USERID

SELECT ACTION,LOCATION,USERID,SUM(CNT1) RECD 
FROM TEST_DUMMY1 GROUP BY ACTION,LOCATION,USERID
HAVING  SUM(CNT1) < 0.5 * (SELECT SUM(CNT1) FROM TEST_DUMMY1 GROUP BY USERID)

output:

ACTION               LOCATION USERID CNT1                   
-------------------- -------- ------ ---------------------- 
DELETED              01       P1     2                      
DELETED              02       P2     8                      
DELETED              01       P1     8                      
INSERTED             01       P1     10                     
INSERTED             02       P2     30                     
DELETED              02       P2     2                      

6 rows selected

ACTION               LOCATION USERID RECD                   
-------------------- -------- ------ ---------------------- 
INSERTED             01       P1     10                     
DELETED              01       P1     10                     
DELETED              02       P2     10                     
INSERTED             02       P2     30                     

4 rows selected

USERID SUM(CNT1)              
------ ---------------------- 
P2     40                     
P1     20    


Error starting at line 6 in command:
SELECT ACTION,LOCATION,USERID,SUM(CNT1) RECD FROM TEST_DUMMY1 GROUP BY ACTION,LOCATION,USERID
HAVING  SUM(CNT1) < 0.5 * (SELECT SUM(CNT1) FROM TEST_DUMMY1 GROUP BY USERID)
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

[Updated on: Wed, 17 December 2008 05:56]

Report message to a moderator

Re: sql problem [message #376427 is a reply to message #376426] Wed, 17 December 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
single-row subquery returns more than one row

Quote:
SELECT SUM(CNT1) FROM TEST_DUMMY1 GROUP BY USERID

It is obvious that this will return more than one row.

You can use, for instance, an inline with the total for each user.

Regards
Michel
Re: sql problem [message #376429 is a reply to message #376420] Wed, 17 December 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at RATIO_TO_REPORT function.

And format your result.

Regards
Michel
Re: sql problem [message #376433 is a reply to message #376425] Wed, 17 December 2008 06:02 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I want to filter the rows based on percentage.

SELECT ACTION,LOCATION,USERID,SUM(CNT1) RECD 
FROM TEST_DUMMY1 GROUP BY ACTION,LOCATION,USERID
HAVING  SUM(CNT1) < 0.5 * (SELECT SUM(CNT1) AS TOTAL FROM TEST_DUMMY1 GROUP BY USERID)



I expect output like below:



COMPARE EACH ROW FOR THE OUTER QUERY ( >50% OF ( RECD/TOTAL )) ,


INSERTED 02 P2 30

[Updated on: Wed, 17 December 2008 06:03]

Report message to a moderator

Re: sql problem [message #376436 is a reply to message #376425] Wed, 17 December 2008 06:07 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I want to comapre RECD of first sql which is less than Total of 2nd sql and return the row

SELECT ACTION,LOCATION,USERID,SUM(CNT1) RECD FROM TEST_DUMMY1 GROUP BY ACTION,LOCATION,USERID;


SELECT userid,SUM(CNT1),SUM(CNT1)*0.5 Total  FROM TEST_DUMMY1 GROUP BY USERID;




Output expected: INSERTED 02 P2 30
Re: sql problem [message #376437 is a reply to message #376425] Wed, 17 December 2008 06:12 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
SELECT ACTION,LOCATION,USERID,SUM(CNT1) RECD FROM TEST_DUMMY1 GROUP BY ACTION,LOCATION,USERID;

where (USERID,RECD) 

(SELECT userid,SUM(CNT1)*0.5 Total  FROM TEST_DUMMY1 GROUP BY USERID);

How to make comparison?

[Updated on: Wed, 17 December 2008 06:12]

Report message to a moderator

Re: sql problem [message #376438 is a reply to message #376437] Wed, 17 December 2008 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can use, for instance, an inline with the total for each user.

Regards
Michel
Re: sql problem [message #376439 is a reply to message #376438] Wed, 17 December 2008 06:18 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Michel, I am not being able to figure out how!
Re: sql problem [message #376440 is a reply to message #376425] Wed, 17 December 2008 06:20 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
And I also want action name in report
Re: sql problem [message #376441 is a reply to message #376439] Wed, 17 December 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the second query inside the FROM clause of the first and join the 2.

You can also use RATIO_TO_REPORT function I mentionned in the other topic you opened at the same time and that I will merge with this one.

Regards
Michel
Re: sql problem [message #376442 is a reply to message #376441] Wed, 17 December 2008 06:24 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
But my query is not as simple query like this. It has 2 tables being joined and with so many parameters

[Updated on: Wed, 17 December 2008 06:24]

Report message to a moderator

Re: sql problem [message #376443 is a reply to message #376442] Wed, 17 December 2008 06:27 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
And how can you insert the second query into first then in the second query I have to add ACTION as group by!
Re: sql problem [message #376449 is a reply to message #376443] Wed, 17 December 2008 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select e.ename, e.sal, round(a.avgsal) avgsal
  2  from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) a
  3  where a.deptno = e.deptno
  4    and e.sal >= a.avgsal
  5  /
ENAME             SAL     AVGSAL
---------- ---------- ----------
BLAKE            2850       1567
ALLEN            1600       1567
FORD             3000       2175
SCOTT            3000       2175
JONES            2975       2175
KING             5000       2917

6 rows selected.

Regards
Michel
Re: sql problem [message #376451 is a reply to message #376449] Wed, 17 December 2008 07:00 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
SELECT a.ACTION,a.LOCATION,a.USERID,round(SUM(CNT1))as RECD FROM TEST_DUMMY1 a,
(SELECT userid,SUM(CNT1)*0.5 as total FROM TEST_DUMMY1 GROUP BY USERID)b
where trim(a.userid)=trim(b.userid)
and a.recd >=b.total
GROUP BY a.ACTION,a.LOCATION,a.USERID;


a.racd invalid identifier!
Re: sql problem [message #376453 is a reply to message #376451] Wed, 17 December 2008 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a select column alias in where clause.

Regards
Michel
Re: sql problem [message #376459 is a reply to message #376453] Wed, 17 December 2008 07:19 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Instead of avg can u give example with sum?
Re: sql problem [message #376462 is a reply to message #376459] Wed, 17 December 2008 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The aggregate function does not matter, it was just an example.

Regards
Michel
Re: sql problem [message #376478 is a reply to message #376462] Wed, 17 December 2008 08:39 Go to previous message
avik2009
Messages: 61
Registered: November 2008
Member
Thank you!!!!!!
Previous Topic: return to For loop
Next Topic: Complex SQL Statement - Comparing Strings
Goto Forum:
  


Current Time: Fri Dec 09 04:20:26 CST 2016

Total time taken to generate the page: 0.10757 seconds