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
Answer / ruchi
SELECT ID,COUNT(*) FROM TABLE1
GROUP BY ID
HAVING COUNT(*)>1
Is This Answer Correct ? | 9 Yes | 0 No |
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 |
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 |
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 ??
Is it possible to have "5 source & 5 Target" in single mapping?
when do we use static cache and when do we use dynamic cache in lookup transformation? with example.
What is confirmed dimension and fact?
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?
what is materialized view?
in static and dynamic which one is better
Mine is Insurance Domain, So interviewer asked about terms like underwriting, disbursement amt, Reinsurance
In which scenario did u used Mapping variable?
Can we get 1st record through Informatica(without sequence number).
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?
From where we can start or use pmcmd?