Home » SQL & PL/SQL » SQL & PL/SQL » plz help me in getting the query
plz help me in getting the query [message #233240] Wed, 25 April 2007 05:43 Go to next message
phanindra
Messages: 1
Registered: April 2007
Location: hyd
Junior Member

i used the following query and get the table accordingly

query : select name,count(name) from sample;

table : name count
red 1
blue 1
green 1
yellow 1




now my requirement is the following table :


name count
red 1
others 3




plz tell me a query in getting the table accordingly.....
Re: plz help me in getting the query [message #233242 is a reply to message #233240] Wed, 25 April 2007 05:52 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Try something like this:

WITH yourtable AS
 (
SELECT 'red'    color FROM dual UNION ALL
SELECT 'blue'   color FROM dual UNION ALL
SELECT 'green'  color FROM dual UNION ALL
SELECT 'yellow' color FROM dual 
 )
SELECT   CASE color
            WHEN 'red'
               THEN 'red'
            ELSE 'others'
         END cnt
       , COUNT (*)
FROM     yourtable
GROUP BY CASE color
            WHEN 'red'
               THEN 'red'
            ELSE 'others'
         END
/


MHE
Previous Topic: Need help in SQL Join and Ranking
Next Topic: functions..
Goto Forum:
  


Current Time: Tue Dec 06 16:26:37 CST 2016

Total time taken to generate the page: 0.09082 seconds