Home » SQL & PL/SQL » SQL & PL/SQL » count of each data for column
count of each data for column [message #194262] Thu, 21 September 2006 06:07 Go to next message
jigarrshah
Messages: 1
Registered: September 2006
Junior Member
Hi All,

I have 1 table say ABC and strucure is like

TABLE - ABC
COLUMN - PST NUMBER
COLUMN - PT VARCHAR2

and data like

PST PT
1 hi
1 hi
1 jig
2 jig
2 jig
3 hi

I need a query whose o/p should like

pst - count(pt for value hi) - count(pt for value jig)
1 2 1
2 0 2
3 1 0

I used query like

select pst, pt, count(pt) as cnt from abc group by pst, pt

and it gives result like

pst pt count(pt)
1 hi 2
1 jig 1
2 hi 0
2 jig 2
3 hi 1
3 jig 0


Re: count of each data for column [message #194264 is a reply to message #194262] Thu, 21 September 2006 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT pst
       ,sum(decode(pt,'hi',1,0))
       ,sum(decode(pt,'jig',1,0))
FROM   abc
GROUP BY pst;


[Edit: D'oh! Missed off the GROUP BY]

[Updated on: Thu, 21 September 2006 06:26]

Report message to a moderator

Re: count for each data of perticulor column [message #194265 is a reply to message #194262] Thu, 21 September 2006 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please don't post questions in multiple forums.
Re: count of each data for column [message #194270 is a reply to message #194264] Thu, 21 September 2006 06:23 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

SELECT pst, sum(decode(pt,'hi',1,0)),sum(decode(pt,'jig',1,0))
FROM abc
GROUP BY pst;

Sandy
Re: count of each data for column [message #194281 is a reply to message #194270] Thu, 21 September 2006 06:58 Go to previous message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Threads merged...

Please do not cross post.
Previous Topic: DISPLAY VALUES IF NULL ALSO (important)
Next Topic: Query needed
Goto Forum:
  


Current Time: Thu Dec 08 10:28:01 CST 2016

Total time taken to generate the page: 0.11498 seconds