Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL server 2008 query help Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 2:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:58 AM
Points: 20, Visits: 38
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.
Post #1423938
Posted Tuesday, February 26, 2013 2:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
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
Post #1423949
Posted Tuesday, February 26, 2013 2:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:58 AM
Points: 20, Visits: 38
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
Post #1423957
Posted Tuesday, February 26, 2013 3:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
Not sure, but I feel this is what you are after

SELECT	L01, LO3, COUNT (*) Counts
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( * ) > 1




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1423967
Posted Tuesday, February 26, 2013 3:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:58 AM
Points: 20, Visits: 38
HI thanks for your help but it came up with an error

incorrect syntax near the keyword 'group'
highlights groub by l01, l03
Post #1423968
Posted Tuesday, February 26, 2013 3:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
mamzy.rahman (2/26/2013)
HI thanks for your help but it came up with an error

incorrect syntax near the keyword 'group'
highlights groub by l01, l03


The derived table - subselect - requires an alias.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1423971
Posted Tuesday, February 26, 2013 3:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
Add alias name to query like C(or any else ) then try

SELECT	L01, LO3, COUNT (*) Counts
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
)C --Here
GROUP BY L01, L03
HAVING COUNT( * ) > 1


--------------------------------------------------------------------------------
Post #1423973
Posted Tuesday, February 26, 2013 3:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
BriPan (2/26/2013)
Add alias name to query like C(or any else ) then try ...



That's what I...never mind.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1423986
Posted Tuesday, February 26, 2013 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:58 AM
Points: 20, Visits: 38
Tis done boys thanks for all your help
Post #1424108
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse