I am having a table with columns
ID NAME
1 x and the requirement is to get the o/p like this
1 y ID Count(*)
1 z 1 3
2 a 2 2
2 b
3 c
so write a sql query to get the id n how many times its
count of repetition n there u shouldn't get the distinct(i.e
id-3)

Reply as early as possible

Answers were Sorted based on User's Feedback



I am having a table with columns ID NAME 1 x a..

Answer / ruchi

SELECT ID,COUNT(*) FROM TABLE1
GROUP BY ID
HAVING COUNT(*)>1

Is This Answer Correct ?    9 Yes 0 No

I am having a table with columns ID NAME 1 x a..

Answer / sambu

select id,count(*) from <table name>
group by id having count(*)>1;

The above gives us desired output

Is This Answer Correct ?    8 Yes 0 No

I am having a table with columns ID NAME 1 x a..

Answer / akash khanwalkar

This one is without using HAVING clause:

SELECT TEMP.ID, TEMP.ID_COUNT FROM
(
SELECT ID AS ID, COUNT(ID) AS ID_COUNT FROM TEMP_ID_TABLE
GROUP BY ID
) TEMP
WHERE TEMP.ID_COUNT > 1;

Is This Answer Correct ?    2 Yes 0 No

Post New Answer

More Informatica Interview Questions

Input flatfile1 has 5 columns, faltfile2 has 3 columns(no common column) output should contain merged data(8 columns) Please let me know how to achieve ??

3 Answers   IBM,


Is it possible to have "5 source & 5 Target" in single mapping?

1 Answers  


when do we use static cache and when do we use dynamic cache in lookup transformation? with example.

1 Answers  


What is confirmed dimension and fact?

7 Answers  


I have a scenario like - how can i load 1st record to Trgt1,2nd->Trgt2, 3rd->Trgt3 and again the cycle has to repeat with loading 4th->Trgt1,5th->Trgt2,6th->Trgt3?

3 Answers   ITC Infotech, TCS,






what is materialized view?

2 Answers  


in static and dynamic which one is better

2 Answers  


Mine is Insurance Domain, So interviewer asked about terms like underwriting, disbursement amt, Reinsurance

0 Answers   TCS, Wipro,


In which scenario did u used Mapping variable?

1 Answers   Cognizant,


Can we get 1st record through Informatica(without sequence number).

0 Answers  


If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b,the target shd be loaded as 1 a+b+c or 1 a||b||c, what transformations shd be used for this?

3 Answers   CTS, Wipro,


From where we can start or use pmcmd?

3 Answers  


Categories