Home » SQL & PL/SQL » SQL & PL/SQL » SQL based on number of count (10.2)
SQL based on number of count [message #362018] Sun, 30 November 2008 00:12 Go to next message
Bangla
Messages: 49
Registered: August 2008
Member
I have a output like below,
 Number	      City	State	   Country	Usage Count
------------- ---------- ------- ---------- ------ -------
66167532	ZONE 1	New York	USA	81
3031807	        ZONE 14	New York	USA	65
75120952	ZONE 1	New York	USA	62
72382236	ZONE 10	New York	USA	60
8795564	        ZONE 5	New York	USA	49
7200128	        ZONE 2	New York	USA	48
3322854	        ZONE 5	New York	USA	43
983797          ZONE 6  UK              UK       1
.
.


Now I like to add more two columns say email and phone number based on usage count column. If usage count column is 1 then corresponding email and phone number will be displayed. If usage count is more than 1 then nothing will be displayed. Like below,

 Number	      City	State	   Country Usage Count email  phone
------------- ---------- ------- ---------- ------ ------- ------  ----------
66167532	ZONE 1	New York	USA	81
3031807	        ZONE 14	New York	USA	65
75120952	ZONE 1	New York	USA	62
72382236	ZONE 10	New York	USA	60
8795564	        ZONE 5	New York	USA	49
7200128	        ZONE 2	New York	USA	48
3322854	        ZONE 5	New York	USA	43
983797          ZONE 6  UK              UK       1  shg@gmail.com 870827382
.
.



How to achieve this scenario through SQL?

[Updated on: Sun, 30 November 2008 00:15]

Report message to a moderator

Re: SQL based on number of count [message #362019 is a reply to message #362018] Sun, 30 November 2008 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmmm, add them.

What can we say without knowing anything about your tables?
Post your table definition (create table statement), post your current query.

Regards
Michel
Re: SQL based on number of count [message #362025 is a reply to message #362018] Sun, 30 November 2008 00:56 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
first result is generated by,
select cc_al.numbers, ct.city,st.state,cn.name,count(cc_sd.id) From listing cc_al
  	LEFT  JOIN dialing cc_sd ON(cc_al.id=cc_sd.al_id )
	LEFT  JOIN city ct ON (cc_al.city_id=ct.id)
	LEFT  JOIN states st ON(ct.state_id=st.id)
	LEFT  JOIN country cn ON(st.country_id=cn.id)
	group by cc_al.numbers, ct.city,st.state,cn.name order by count(cc_sd.id) desc ;


Now from two more tables emails and phones there is column e_mail and phone_no. I need to add them in this list based on count of number 1.
Re: SQL based on number of count [message #362029 is a reply to message #362025] Sun, 30 November 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add outer joins.

Regards
Michel
Re: SQL based on number of count [message #362030 is a reply to message #362025] Sun, 30 November 2008 01:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Add two more joins and use either decode or case to determine whether or not to display the values.
Re: SQL based on number of count [message #362034 is a reply to message #362018] Sun, 30 November 2008 01:32 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
Used decode but that did not solve the problem as there is group by clause.
Re: SQL based on number of count [message #362035 is a reply to message #362034] Sun, 30 November 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if there is a group by clause you can use decode.

Regards
Michel
Re: SQL based on number of count [message #362039 is a reply to message #362018] Sun, 30 November 2008 02:09 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Hello micheal .

how could i achieve when using of both decode and group by ?.

since i know that we cant use decode function in group by clauses.please clarify.








Thanks
Seyed mohamed
Re: SQL based on number of count [message #362051 is a reply to message #362039] Sun, 30 November 2008 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select e.deptno, count(*), 
  2         decode(count(*),5,dname) dname -- name only if count is 5
  3  from emp e, dept d
  4  where d.deptno (+) = e.deptno
  5  group by e.deptno, dname
  6  /
    DEPTNO   COUNT(*) DNAME
---------- ---------- --------------
        10          3
        20          5 RESEARCH
        30          6

3 rows selected.

Regards
Michel
Re: SQL based on number of count [message #362052 is a reply to message #362039] Sun, 30 November 2008 05:02 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@seyed456,
seyed456 wrote on Sun, 30 November 2008 13:39
since i know that we cant use decode function in group by clauses.please clarify.



I didn't quite get what you meant by this. See the following codes:
SQL> desc emp;
 Name                         Null?    Type
 ---------------------------- -------- -----------------------

 EMPNO                        NOT NULL NUMBER(4)
 ENAME                                 VARCHAR2(10)
 JOB                                   VARCHAR2(9)
 MGR                                   NUMBER(4)
 HIREDATE                              DATE
 SAL                                   NUMBER(7,2)
 COMM                                  NUMBER(7,2)
 DEPTNO                                NUMBER(2)

SQL> SELECT sal FROM emp;

       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100

       SAL
----------
       950
      3000
      1300

14 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  SELECT DECODE(SIGN(1500-sal), 1, 'Sal less than or equal to 1500',
  2  -1, 'Sal greater than 1500',
  3  'Sal less than or equal to 1500' ) sal_compare,
  4  COUNT(*) FROM emp
  5  GROUP BY DECODE(SIGN(1500-sal), 1, 'Sal less than or equal to 1500',
  6* -1, 'Sal greater than 1500', 'Sal less than or equal to 1500')
  7  ;

SAL_COMPARE                      COUNT(*)
------------------------------ ----------
Sal greater than 1500                   7
Sal less than or equal to 1500          7




Hope this helps.

Regards,
Jo
Previous Topic: SQL - max(count(department_id))
Next Topic: using the SYS_GUID() function
Goto Forum:
  


Current Time: Wed Dec 07 16:42:32 CST 2016

Total time taken to generate the page: 0.14648 seconds