CODEWITHSUNDEEP
sqlsql-server
shorton
shorton

Reputation: 353

count based on 2 fields result in 3rd

SQL Server. Novice. Not sure how to phrase my question. Try via example:

For a table with these fields and values:

uniqueV, state, locationcode1, locationcode2, typecode 
01, TN, 1, X
02, TN, 1, X
03, TN, 1, Y
04, KY, 2, Z
05, KY, 1, R
06, KY, 1, R
07, KY, 1, R

I need a result of

01, TN, 1, X, 2 -- e.g. 2 occurrences of 1,X in TN
02, TN, 1, X, 2
03, TN, 1, Y, 1
04, KY, 2, Z, 1
05, KY, 1, R, 3
06, KY, 1, R, 3 
07, KY, 1, R, 3

Where this new, 5th field is a count of the occurrences of [typecode] pair within each of state

========================================================

Follow-up please

Any way to produce an additional output Column with a count of a particular typecode within a State, locationcode pair. For example a count of 'X' records

So 

uniqueV, state, locationcode1, locationcode2, typecode 
01, TN, 1, X
02, TN, 1, X
03, TN, 1, Y
04, KY, 2, Z
05, KY, 1, R
06, KY, 1, R
07, KY, 1, R

resulting in

01, TN, 1, X, 2 -- e.g. for TN, 1 there are 2 occurances of 'X' records.
02, TN, 1, X, 2
03, TN, 1, Y, 2
04, KY, 2, Z, 0
05, KY, 1, R, 0
06, KY, 1, R, 0
07, KY, 1, R, 0

I thought I could extrapolate myself but was wrong :(

Upvotes: 2

Views: 58

Answers (2)

Aura
Aura

Reputation: 1307

I am sure there is more efficient solution to this problem, but I think for now this can help:

WITH cte_base AS (
SELECT uniqueV, state, locationcode1, locationcode2,
COUNT(1) OVER (PARTITION BY state, locationcode1, locationcode2) AS 'typecode'
FROM table_name
)
SELECT uniqueV, state, locationcode1, locationcode2, 
CASE WHEN state  = 'TN' AND locationcode1 THEN typecode 
ELSE '0' END AS 'typecode2'
FROM cte_base

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

I think you need something like:

SELECT *, 
(
SELECT COUNT(*) 
FROM YourTable AS T1
WHERE T1.state = T2.state AND T1.locationcode1 = T2.locationcode1
      AND T1.locationcode2 = T2.locationcode2) AS Occur
FROM YourTable AS T2

Upvotes: 4

Related Questions