CODEWITHSUNDEEP
sqlpostgresql
sannan14
sannan14

Reputation: 79

query to count occurances of aparticular column value

Let's say I have a table with the following value

1
1
1
2
2
2
3
3
3
1
1
1
2
2
2

I need to get an out put like this, which counts each occurances of a particular value

 1  1
 1  2
 1  3
 2  1
 2  2
 2  3
 3  1
 3  2 
 3  3
 1  1
 1  2
 1  3
 2  1
 2  2
 2  3

NB: This is a sample table Actual table is a complex table with lots of rows and columns and query contains some more conditions

Upvotes: 0

Views: 109

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

SQL Server provide row_number() function :

select ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) RN FROM <TABLE_NAME>

EDIT :

select * , case when (row_number() over (order by (select 1))) %3 = 0 then 3 else
           (row_number() over (order by (select 1))) %3 end [rn] from table

Upvotes: 1

Hambone
Hambone

Reputation: 16397

I think there is a problem with your sample, in that you have an implied order but not an explicit one. There is no guarantee that the database will keep and store the values the way you have them listed, so there has to be some inherent/explicit ordering mechanism to tell the database to give those values back exactly the way you listed.

For example, if you did this:

update test
set val = val + 2
where val < 3

You would find your select * no longer comes back the way you expected.

You indicated your actual table was huge, so I assume you have something like this you can use. There should be something in the table to indicate the order you want... a timestamp, perhaps, or maybe a surrogate key.

That said, assuming you have something like that and can leverage it, I believe a series of windowing functions would work.

with rowed as (
  select
    val,
    case
      when lag (val, 1, -1) over (order by 1) = val then 0
      else 1
    end as idx,
    row_number() over (order by 1) as rn  -- fix this once you have your order
  from
    test
),
partitioned as (
  select
    val, rn,
    sum (idx) over (order by rn) as instance
  from rowed
)
select
  val, instance, count (1) over (partition by instance order by rn)
from
  partitioned

This example orders by the way they are listed in the database, but you would want to change the row_number function to accommodate whatever your real ordering mechanism is.

1   1   1
1   1   2
1   1   3
2   2   1
2   2   2
2   2   3
3   3   1
3   3   2
3   3   3
1   4   1
1   4   2
1   4   3
2   5   1
2   5   2
2   5   3

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35603

If the number repeats over different "islands" then you need to calculate a value to maintain those islands first (grpnum). That first step can be undertaken by subtracting a raw top-to-bottom row number (raw_rownum) from a partitioned row number. That result gives each "island" a reference unique to that island that can then be used to partition a subsequent row number. As each order by can disturb the outcome I find it necessary to use individual steps and to pass the prior calculation up so it may be reused.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1  ([num] int);

INSERT INTO Table1  ([num])
VALUES  (1),(1),(1),(2),(2),(2),(3),(3),(3),(1),(1),(1),(2),(2),(2);

Query 1:

select
    num
    , row_number() over(partition by (grpnum + num) order by raw_rownum) rn
    , grpnum + num island_num
from (
    select
           num
         , raw_rownum - row_number() over(partition by num order by raw_rownum) grpnum
         , raw_rownum
    from (
          select
              num
             , row_number() over(order by (select null)) as raw_rownum
          from table1
         ) r
     ) d
;

Results:

| num | rn | island_num |
|-----|----|------------|
|   1 |  1 |          1 |
|   1 |  2 |          1 |
|   1 |  3 |          1 |
|   2 |  1 |          5 |
|   2 |  2 |          5 |
|   2 |  3 |          5 |
|   1 |  1 |          7 |
|   1 |  2 |          7 |
|   1 |  3 |          7 |
|   3 |  1 |          9 |
|   3 |  2 |          9 |
|   3 |  3 |          9 |
|   2 |  1 |         11 |
|   2 |  2 |         11 |
|   2 |  3 |         11 |

Upvotes: 2

Related Questions