CODEWITHSUNDEEP
sqlsql-server-2008-r2countdistinct
Toby Derrum
Toby Derrum

Reputation: 309

SQL to add an additional column that counts duplicate values for a particular column

I'm seem to be having an off day and can't seem to get this query working.

Initially my query without the use of DISTINCT included the repetition of 2 rows:

STAT       SHNO      UPDD         UPDT       ORDERNO 
40       ASN123    20141022   150048       40303020
30       ASN124    20141022   150048       40303021
30       ASN124    20141022   150048       40303021
40       ASN123    20141022   150048       40303020
30       ASN125    20141022   150048       40303021

I changed the query to include DISTINCT:

SELECT DISTINCT  
    STAT, SHNO, UPDD, UPDT, ORDERNO
FROM   
    BLUEWATER

and I now get the desired resultset: 

STAT         SHNO         UPDD        UPDT      ORDERNO
30         SHN124       20141022      150048      40303021
30         SHN125       20141022      150048      40303021
40         SHN123       20141022      150048      40303020

However what I am trying to do is use COUNT to generate a column in my results that counts the rows where ORDERNO is not unique and wish to obtain the following:

STAT         SHNO         UPDD        UPDT      ORDERNO      ORDERNOCOUNT     
30         SHN124       20141022      150048    40303021      2                
30         SHN125       20141022      150048    40303021      2                
40         SHN123       20141022      150048    40303020      1

Upvotes: 0

Views: 119

Answers (1)

Hogan
Hogan

Reputation: 70513

I'd just use group by and COUNT() OVER ... what version are you using?

SELECT STAT, SHNO, UPDD, UPDT, ORDERNO, 
       COUNT(*) OVER (PARTITION BY ORDERNO) AS ORDERNOCOUNT
FROM BLUEWATER     
GROUP BY STAT, SHNO, UPDD, UPDT, ORDERNO

as sub-select

SELECT gsub.STAT, gsub.SHNO, gsub.UPDD, gsub.UPDT, gsub.ORDERNO, sub.c AS ORDERNOCOUNT
FROM 
(
   SELECT STAT, SHNO, UPDD, UPDT, ORDERNO
   FROM BLUEWATER
   GROUP BY STAT, SHNO, UPDD, UPDT, ORDERNO
) gsub
JOIN (
   SELECT ORDERNO, COUNT(*) as c
   FROM BLUEWATER
   GROUP BY ORDERNO
) as sub ON gsub.ORDERNO = sub.ORDERNO

Upvotes: 1

Related Questions