Query to find occurence
Last Post 30 Jan 2013 01:25 AM by Titus Aguilar. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
mukesh kumar
New Member
New Member
Posts:14

--
29 Jan 2013 02:06 PM

 Hi Guys,

 

I have one scenario for which source data looks like:

ID        VAL

10        A

10        A

10        B

11        A

12        B

 

My output should look like this

 

ID                COUNT(Here count =no of distinct VAL that particular ID has in all the rows)

10                    2

11                    1

12                    1

 

Let me know If you guys need any clarification.

 

Rex Reese
New Member
New Member
Posts:44

--
29 Jan 2013 02:38 PM
Based on your source data, shouldn't the results look like this? (If i'm understanding correctly)

ID COUNT
10 3
11 1
12 1

If so then try this:


select ID,count(ID)
from myFile
group by ID
Crispin
Veteran Member
Veteran Member
Posts:1501

--
29 Jan 2013 02:38 PM

SELECT ID, COUNT(ID) FROM MYFILE ORDER BY ID GROUP BY ID

Crispin.

Brian Rusch
Advanced Member
Advanced Member
Posts:555

--
29 Jan 2013 03:16 PM
Select ID, COUNT(DISTINCT VAL) from myfile group by ID order by ID
mukesh kumar
New Member
New Member
Posts:14

--
29 Jan 2013 11:14 PM

Thanks for the answer.But I don't want the count of particular Val.I want count of type of VAL.

for eg. here 10 has 2 types of val A and B..so count will be 2

again  ID 11 has only type of val and so ID 12.

 

sorry for the confusion.

Titus Aguilar
Veteran Member
Veteran Member
Posts:576
Avatar

--
30 Jan 2013 01:25 AM
How about this:

select id, count(distinct val) 
from myfile
group by id
 


Result is:

ID COUNT
10 2
11 1
12 1


Hth,
Titus

P.S.: Same solution as Brian's in retrospect.
You are not authorized to post a reply.

Acceptable Use Policy