February 26, 2013 at 2:30 am
Hi guys im trying to show all the duplicate data from a table how would one do this. this is what i have tried:
SELECT L01, COUNT (DISTINCT L03)
FROM (
SELECT A.L01, A.L03, A.L39, B.A04, B.A10, B.A31
FROM [LEARNER_SN05]AS A
INNER JOIN (SELECT L01, L03, A04, A10, A31
FROM [AIMS_SN05]
WHERE A04 <> 35
AND A10 IN (10, 22, 81)
AND A31 IS NOT NULL
GROUP BY L01, L03, A04, A10, A31) AS B ON B.L01 = A.L01 AND B.L03 = A.L03
WHERE A.L39 = 95
GROUP BY A.L01, A.L03, A.L39, B.A04, B.A10, B.A31 )
GROUP BY L01, L03
HAVING (COUNT (DISTINCT L03) > 1)
basically i first need to list all the l01 and l03
where a04 <>35
and a10 = 10,22,81
and a31 is not null
and where l39 = 95
please notice there is 2 tables in the query the aims table and the learner table.
February 26, 2013 at 2:47 am
I am not getting you.. will you provide some schema of table and sample data and what you
want (result data).
which help us for proper answer
February 26, 2013 at 2:59 am
hi mate
basically this part of the query
SELECT A.L01, A.L03, A.L39, B.A04, B.A10, B.A31
FROM [LEARNER_SN05]AS A
INNER JOIN (SELECT L01, L03, A04, A10, A31
FROM [AIMS_SN05]
WHERE A04 <> 35
AND A10 IN (10, 22, 81)
AND A31 IS NOT NULL
GROUP BY L01, L03, A04, A10, A31) AS B ON B.L01 = A.L01 AND B.L03 = A.L03
WHERE A.L39 = 95
GROUP BY A.L01, A.L03, A.L39, B.A04, B.A10, B.A31
gives me this
L01 L03 L39 A04 A10 A31
1 123 95 10 22 01/09/1990
1 234 95 22 22 02/09/1990
2 345 95 32 10 03/09/1990
3 456 95 45 81 19/09/2012
4 567 95 10 22 10/10/2012
4 567 95 10 22 15/10/2012
5 678 95 11 81 01/09/1990
and i want to show the number of l03 that are duplicates so in this case 567 record shows up twice
February 26, 2013 at 3:20 am
Not sure, but I feel this is what you are after
SELECTL01, LO3, COUNT (*) Counts
FROM(
SELECTA.L01, A.L03, A.L39, B.A04, B.A10, B.A31
FROM[LEARNER_SN05]AS A
INNER JOIN(
SELECTL01, L03, A04, A10, A31
FROM[AIMS_SN05]
WHEREA04 <> 35
ANDA10 IN (10, 22, 81)
ANDA31 IS NOT NULL
GROUP BY L01, L03, A04, A10, A31
) AS B ON B.L01 = A.L01 AND B.L03 = A.L03
WHEREA.L39 = 95
GROUP BY A.L01, A.L03, A.L39, B.A04, B.A10, B.A31
)
GROUP BY L01, L03
HAVING COUNT( * ) > 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 26, 2013 at 3:26 am
HI thanks for your help but it came up with an error
incorrect syntax near the keyword 'group'
highlights groub by l01, l03
February 26, 2013 at 3:34 am
mamzy.rahman (2/26/2013)
HI thanks for your help but it came up with an errorincorrect syntax near the keyword 'group'
highlights groub by l01, l03
The derived table - subselect - requires an alias.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 26, 2013 at 3:37 am
Add alias name to query like C(or any else ) then try
SELECTL01, LO3, COUNT (*) Counts
FROM(
SELECTA.L01, A.L03, A.L39, B.A04, B.A10, B.A31
FROM[LEARNER_SN05]AS A
INNER JOIN(
SELECTL01, L03, A04, A10, A31
FROM[AIMS_SN05]
WHEREA04 <> 35
ANDA10 IN (10, 22, 81)
ANDA31 IS NOT NULL
GROUP BY L01, L03, A04, A10, A31
) AS B ON B.L01 = A.L01 AND B.L03 = A.L03
WHEREA.L39 = 95
GROUP BY A.L01, A.L03, A.L39, B.A04, B.A10, B.A31
)C --Here
GROUP BY L01, L03
HAVING COUNT( * ) > 1
--------------------------------------------------------------------------------
February 26, 2013 at 3:55 am
BriPan (2/26/2013)
Add alias name to query like C(or any else ) then try ...
That's what I...never mind.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 26, 2013 at 8:29 am
Tis done boys thanks for all your help
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply