0

The data in my table looks like this: 

AUDTORG | SEQUENCENO | LINE | REFERENCE | AMOUNT | 
--------------------------------------------------
ADX     | 1          | 422  | Wire1     | 10     | 
ADX     | 2          | 385  | Wire2     | 10     | 
ADX     | 2          | 335  | Wire25    | 10     | 
ADX     | 2          | 335  | Transfer  | 10     | 
BMD     | 1          | 555  | Wire4     | 10     |

I have to: 1. concatenate: AUDTORG and SEQUENCENO 2. Count the occurence of the concatenated field NewID.

This is the result I'm looking for:

AUDTORG | SEQUENCENO | LINE | REFERENCE | AMOUNT | NewID  | Occurence |
-----------------------------------------------------------------------
ADX     | 1          | 422  | Wire1     | 10     | ADX1   | 1         |
ADX     | 2          | 385  | Wire2     | 10     | ADX2   | 3         |
ADX     | 2          | 335  | Wire25    | 10     | ADX2   | 3         |
ADX     | 2          | 335  | Transfer  | 10     | ADX2   | 3         |
BMD     | 1          | 555  | Wire4     | 10     | BMD1   | 1         |

Here is the code I tried:

SELECT *, BKENTD.AUDTORG + CAST(BKENTD.SEQUENCENO AS varchar) as NewID,
count(BKENTD.AUDTORG + CAST(BKENTD.SEQUENCENO AS varchar)) as Occurence
FROM ADXDAT.dbo.BKENTD

All the rows must remain.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Seva
  • 57
  • 10

2 Answers2

1

All rows must remain makes for the need for a subquery. This will obtain counts:

select BKENTD.AUDTORG + CAST(BKENTD.SEQUENCENO AS varchar) as NewID, count(*) Occurence
FROM ADXDAT.dbo.BKENTD
group by BKENTD.AUDTORG + CAST(BKENTD.SEQUENCENO AS varchar)

You now have the NewID and count for each. Join it back to your first statement and refer to it as a standard column (select * is now select a.* so you don't get the b.newID field):

SELECT a.*, BKENTD.AUDTORG + CAST(BKENTD.SEQUENCENO AS varchar) as NewID, Occurence
FROM ADXDAT.dbo.BKENTD a
inner join 
(select BKENTD.AUDTORG + CAST(BKENTD.SEQUENCENO AS varchar) as NewID, count(*) Occurence
FROM ADXDAT.dbo.BKENTD
group by BKENTD.AUDTORG + CAST(BKENTD.SEQUENCENO AS varchar)) b
on a.NewID = b.NewID
Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • how do I "join it back to my first statement and refer to it as ... "? I'm not familiar with subqueries, thanks!! – Seva Feb 06 '14 at 19:07
  • @seva - I've done it for you, the second select statement there has the join to the subquery done. I showed you the logic breakdown so you can understand it instead of just the answer :) – Twelfth Feb 06 '14 at 19:14
  • I'm having a error msg: Incorrect synthax near the keyword 'FROM'. Not sure if that makes a difference, I'm using Microsoft SQL Server 2008 and the query is entered in the "Command Text" in Excel through an ODBC Connection. – Seva Feb 06 '14 at 20:10
  • stupid error on my part...the subquery says from from instead of just from. Corrected, try again. ODBC through excel doesn't make much of a difference in syntax. – Twelfth Feb 06 '14 at 21:15
  • I wasn't able to make it work, got a msg: Invalid column name 'NewId'. Thank you for your time, the solution proposed by @Gordon Linoff did work. However, I would have liked to understand the logic behind the subquery. – Seva Feb 07 '14 at 15:42
1

You can do this using window functions:

SELECT b.*,
      (b.AUDTORG + CAST(b.SEQUENCENO AS varchar(255)) )as NewID,
      count(*) over (partition by b.AUDTORG + CAST(b.SEQUENCENO AS varchar(255)) ) as Occurence
FROM ADXDAT.dbo.BKENTD b;

The over clause is used by window function. In this case, it counts everything in a group. The group is defined by the partition by clause. So, it counts everything with the same value of the new id.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786