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

Question

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

Please tag with the version of SQL Server you're using, so we don't have to guess. Also, the fact that you need `DISTINCT` in the first place is a clue that there is a fundamental problem with the underlying design. (Because of the cryptic column names - who named those? - it's largely impossible for us to guess at what the design problems might be.) — Aaron Bertrand, Oct 23 '14 at 17:18
The names are cryptic to me also and I received a spreadsheet with description for only some of the columns although I won't need them all. I don't have client access and don't find it practical to always connect via VPN so I recreated the DB2 table in SQL Server R2. Thanks for your help ! — Toby Derrum, Oct 23 '14 at 17:30

Hogan · Accepted Answer · 2014-10-23T20:26:06.633

1

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

edited Oct 23 '14 at 20:26

answered Oct 23 '14 at 17:12

Hogan

69,564
10
76
117

Well you would get 1 instead of 2 for the first two results, because `SHNO` changes... – Aaron Bertrand Oct 23 '14 at 17:14
1

Seems more appropriate to use `COUNT(*) OVER (PARTITION BY ORDERNO)` but, since the OP failed to bother to mention what version they're using... – Aaron Bertrand Oct 23 '14 at 17:15
Thanks @Aaron. I am using SQL Server 2008, version 10.50.1617 but I will be porting the queries to DB2 running on the iseries AS400 – Toby Derrum Oct 23 '14 at 17:18
@TobyDerrum: v10.50 is SQL Server **2008 R2** (which is **not** the same as **2008** ....) – marc_s Oct 23 '14 at 17:24
@TobyDerrum -- DB2 has many versions too. But if you are using 9.7 or after you should be fine. It will support COUNT...OVER – Hogan Oct 23 '14 at 17:25
Unfortunately it gave me the following on DB2: `java.sql.SQLException: [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.` – Toby Derrum Oct 23 '14 at 17:52
@TobyDerrum - What version of DB2 do you have? – Hogan Oct 23 '14 at 18:14
I dont know. I doubt they'd risk upgrading so I'm guessing fairly old. I ran every command I found here: http://stackoverflow.com/questions/3821795/how-to-check-db2-version but got nothing back. Best I can do is ask tomorrow. – Toby Derrum Oct 23 '14 at 18:16
@TobyDerrum - I wrote a version that uses a sub-select above -- I didn't test it so it might have a typo -- but should get you in the right direction. – Hogan Oct 23 '14 at 18:21
Thanks, much appreciated ! I tried this version on both DBs and keep getting `java.sql.SQLException: [SQL0203] Name ORDERNO is ambiguous.` – Toby Derrum Oct 23 '14 at 18:59
@TobyDerrum - ok... put `sub` or `BLUEWATER` in front of `ORDERNO` on the select and group by line – Hogan Oct 23 '14 at 19:15
@Hogan I get `java.sql.SQLException: [SQL0122] Column C or expression in SELECT list not valid.`. There is no error when I add sub.C to the last GROUP BY line but it gives me a wrong count for ORDERNOCOUNT – Toby Derrum Oct 23 '14 at 19:48
@TobyDerrum - ok I gave it another shot. I've been spoiled by the windowing functions. – Hogan Oct 23 '14 at 20:25
@Hogan I appreciate your efforts. It is still calculating the ORDERNOCOUNT from the total ORDERNO count in the table not those on display. – Toby Derrum Oct 23 '14 at 21:03
@TobyDerrum - Yes that is what you wanted -- a count of the number of orders with that ordernumber. If you want something else you will have to explain what it is. – Hogan Oct 23 '14 at 21:19

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

1 Answers1