removing duplicate rows in SQL

[login to unmask email]

removing duplicate rows in SQL
Can someone tell me how to remove duplicate rows in this SQL query? I
cannot seem to get the desired results as I get 8 rows returned - a row
for each combination of code and type. I have used MAX, COUNT, GROUP BY,
HAVING and still no luck. Any info is appreciated.

Table1:
code integer
type char(1)

Sample Data:
100 N
101 N
100 N
100 D
100 N
101 D
101 N
102 N
103 N
103 D
102 N
100 N
102 D

RESULTS:
CODE NCOUNT DCOUNT
100 4 1
101 2 1
102 2 1
103 1 1

Thanks,


Michelle



John McKown

Re: removing duplicate rows in SQL
(in response to mmetcalf@NOTES.STATE.NE.US)
SELECT DISTINCT ...

--
John McKown
Senior Technical Specialist
UICI Insurance Center
Applications & Solutions Team
+1.817.255.3225


> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Wednesday, December 11, 2002 2:42 PM
> To: [login to unmask email]
> Subject: removing duplicate rows in SQL
>
>
> Can someone tell me how to remove duplicate rows in this SQL query? I
> cannot seem to get the desired results as I get 8 rows
> returned - a row
> for each combination of code and type. I have used MAX,
> COUNT, GROUP BY,
> HAVING and still no luck. Any info is appreciated.
>
> Table1:
> code integer
> type char(1)
>
> Sample Data:
> 100 N
> 101 N
> 100 N
> 100 D
> 100 N
> 101 D
> 101 N
> 102 N
> 103 N
> 103 D
> 102 N
> 100 N
> 102 D
>
> RESULTS:
> CODE NCOUNT DCOUNT
> 100 4 1
> 101 2 1
> 102 2 1
> 103 1 1
>
> Thanks,
>
>
> Michelle
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the > list can be
>
>



Terry Purcell

Re: removing duplicate rows in SQL
(in response to John McKown)
Michelle,

V7:

SELECT TYPE, COUNT(CASE CODE_COL WHEN 'N' THEN '' END) AS NCOUNT
, COUNT(CASE CODE_COL WHEN 'D' THEN '' END) AS DCOUNT
FROM SAMPLE
GROUP BY TYPE

Prior to V7 you need to use SUM (rather than COUNT), and 1 rather than ''.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of
[login to unmask email]
Sent: Wednesday, December 11, 2002 2:42 PM
To: [login to unmask email]
Subject: removing duplicate rows in SQL


Can someone tell me how to remove duplicate rows in this SQL query? I
cannot seem to get the desired results as I get 8 rows returned - a row
for each combination of code and type. I have used MAX, COUNT, GROUP BY,
HAVING and still no luck. Any info is appreciated.

Table1:
code integer
type char(1)

Sample Data:
100 N
101 N
100 N
100 D
100 N
101 D
101 N
102 N
103 N
103 D
102 N
100 N
102 D

RESULTS:
CODE NCOUNT DCOUNT
100 4 1
101 2 1
102 2 1
103 1 1

Thanks,


Michelle








John W. Herbold

Re: removing duplicate rows in SQL
(in response to Terry Purcell)
Select distinct?

Thanks,

John W. Herbold Jr.
IS Specialist/DBA


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 11, 2002 2:42 PM
To: [login to unmask email]
Subject: removing duplicate rows in SQL

Can someone tell me how to remove duplicate rows in this SQL query? I
cannot seem to get the desired results as I get 8 rows returned - a row
for each combination of code and type. I have used MAX, COUNT, GROUP BY,
HAVING and still no luck. Any info is appreciated.

Table1:
code integer
type char(1)

Sample Data:
100 N
101 N
100 N
100 D
100 N
101 D
101 N
102 N
103 N
103 D
102 N
100 N
102 D

RESULTS:
CODE NCOUNT DCOUNT
100 4 1
101 2 1
102 2 1
103 1 1

Thanks,


Michelle








Abhijit Sinha

Re: removing duplicate rows in SQL
(in response to John W. Herbold)
Try this:

db2 "select distinct tab1.code, tab1.ncount, tab2.dcount from (select code,
count(code) as ncount from Table1 where type = 'N' group by code) as tab1,
(select code, count(code) as dcount from Table1 where type = 'D' group by
code) as tab2"

Abhijit


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 11, 2002 3:42 PM
To: [login to unmask email]
Subject: removing duplicate rows in SQL


Can someone tell me how to remove duplicate rows in this SQL query? I
cannot seem to get the desired results as I get 8 rows returned - a row
for each combination of code and type. I have used MAX, COUNT, GROUP BY,
HAVING and still no luck. Any info is appreciated.

Table1:
code integer
type char(1)

Sample Data:
100 N
101 N
100 N
100 D
100 N
101 D
101 N
102 N
103 N
103 D
102 N
100 N
102 D

RESULTS:
CODE NCOUNT DCOUNT
100 4 1
101 2 1
102 2 1
103 1 1

Thanks,


Michelle








Ashish Mohan

Re: removing duplicate rows in SQL
(in response to Abhijit Sinha)
SELECT COALESCE(T1.CODE,T2.CODE) CODE,
VALUE(T1.NCOUNT,0) NCOUNT,
VALUE(T2.DCOUNT,0) DCOUNT
FROM
(
SELECT CODE, COUNT(*) AS NCOUNT
FROM TAB T1
WHERE TYPE = 'N'
GROUP BY CODE
) T1
FULL OUTER JOIN
(
SELECT CODE, COUNT(*) AS DCOUNT
FROM TAB T1
WHERE TYPE = 'D'
GROUP BY CODE
) T2
ON T1.CODE = T2.CODE

Thanks.
Ashish.

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 11, 2002 12:42 PM
To: [login to unmask email]
Subject: removing duplicate rows in SQL

Can someone tell me how to remove duplicate rows in this SQL query? I
cannot seem to get the desired results as I get 8 rows returned - a row
for each combination of code and type. I have used MAX, COUNT, GROUP BY,
HAVING and still no luck. Any info is appreciated.

Table1:
code integer
type char(1)

Sample Data:
100 N
101 N
100 N
100 D
100 N
101 D
101 N
102 N
103 N
103 D
102 N
100 N
102 D

RESULTS:
CODE NCOUNT DCOUNT
100 4 1
101 2 1
102 2 1
103 1 1

Thanks,


Michelle








Marco Poma

Re: removing duplicate rows in SQL
(in response to Ashish Mohan)
Have you tried DISTINCT???

Select DISTINCT COLNAME, COLNAME.....

[login to unmask email] wrote:

>Can someone tell me how to remove duplicate rows in this SQL query? I
>cannot seem to get the desired results as I get 8 rows returned - a row
>for each combination of code and type. I have used MAX, COUNT, GROUP BY,
>HAVING and still no luck. Any info is appreciated.
>
>Table1:
> code integer
> type char(1)
>
>Sample Data:
> 100 N
> 101 N
> 100 N
> 100 D
> 100 N
> 101 D
> 101 N
> 102 N
> 103 N
> 103 D
> 102 N
> 100 N
> 102 D
>
>RESULTS:
> CODE NCOUNT DCOUNT
> 100 4 1
> 101 2 1
> 102 2 1
> 103 1 1
>
>Thanks,
>
>
>Michelle
>
>
>
>
>
>



[login to unmask email]

Re: removing duplicate rows in SQL
(in response to Marco Poma)
Thanks to all who replied! I neglected to mention that I had also tried
DISTINCT as well as some of you but still had the duplicate rows. Then
figured out that I didn't need the DISTINCT because the GROUP BY should
weed out the duplicates. I made this alot harder than it needed to be as I
had the following query (one of the answers) with alot more specified!!

One variation to get desired results:
=======
SELECT CODE
, SUM(CASE TYPE WHEN 'N' THEN 1 ELSE 0 END) AS NCOUNT
, SUM(CASE TYPE WHEN 'D' THEN 1 ELSE 0 END) AS DCOUNT
FROM TABLE1
GROUP BY CODE;

=================
Original question:
=================
Subject: removing duplicate rows in SQL

Can someone tell me how to remove duplicate rows in this SQL query? I
cannot seem to get the desired results as I get 8 rows returned - a row
for each combination of code and type. I have used MAX, COUNT, GROUP BY,
HAVING and still no luck. Any info is appreciated.

Table1:
code integer
type char(1)

Sample Data:
100 N
101 N
100 N
100 D
100 N
101 D
101 N
102 N
103 N
103 D
102 N
100 N
102 D

RESULTS:
CODE NCOUNT DCOUNT
100 4 1
101 2 1
102 2 1
103 1 1

Thanks,


Michelle



Dave Nance

Re: removing duplicate rows in SQL
(in response to mmetcalf@NOTES.STATE.NE.US)
Michelle,
Probably won't make much of a difference in the case of just 4 rows as in your example. You should try using null instead of 0 on the rows you don't want to count. Terry Purcell had mentioned this at IDUG earlier this year. I've tried it out on a few of our reports and made quite a difference on the response times.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/12/02 7:48:43 AM >>>
Thanks to all who replied! I neglected to mention that I had also tried
DISTINCT as well as some of you but still had the duplicate rows. Then
figured out that I didn't need the DISTINCT because the GROUP BY should
weed out the duplicates. I made this alot harder than it needed to be as I
had the following query (one of the answers) with alot more specified!!

One variation to get desired results:
=======
SELECT CODE
, SUM(CASE TYPE WHEN 'N' THEN 1 ELSE 0 END) AS NCOUNT
, SUM(CASE TYPE WHEN 'D' THEN 1 ELSE 0 END) AS DCOUNT
FROM TABLE1
GROUP BY CODE;

=================
Original question:
=================
Subject: removing duplicate rows in SQL

Can someone tell me how to remove duplicate rows in this SQL query? I
cannot seem to get the desired results as I get 8 rows returned - a row
for each combination of code and type. I have used MAX, COUNT, GROUP BY,
HAVING and still no luck. Any info is appreciated.

Table1:
code integer
type char(1)

Sample Data:
100 N
101 N
100 N
100 D
100 N
101 D
101 N
102 N
103 N
103 D
102 N
100 N
102 D

RESULTS:
CODE NCOUNT DCOUNT
100 4 1
101 2 1
102 2 1
103 1 1

Thanks,


Michelle




This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.