CODEWITHSUNDEEP
sqlsql-servercount
Yalda
Yalda

Reputation: 684

Sql Server Count some rows and leave the rest

I have a select query which results in the table below:

RowNo |  ComponentRef | ComponentTitle

  1   |      NULL     |   UsersCount5
  2   |      NULL     |   UsersCount1 
  3   |      NULL     |   UsersCount1
  4   |      NULL     |   UsersCount1
  5   |       2       |   SomeComponent
  6   |       3       |   SomeOtherComponent
  7   |       4       |   YetAnotherComponent

I need to have and output like this:

RowNo |  ComponentRef | ComponentTitle

  1   |      NULL     |   UsersCount8               ===> (5+1+1+1)
  2   |       2       |   SomeComponent
  3   |       3       |   SomeOtherComponent
  4   |       4       |   YetAnotherComponent

I don't know how to count the rows with ComponentTitle starting with "UsersCount" and just leave the rest of the rows as they are.

Any help is appreciated.

P.S.1: Forget about the RowNo column. I can handle that :)

P.S.2: Instead of UsersCount8 you could just tell me how to put the Count of those specific rows(In this case, 4). I will handle playing with the string and stuff :)

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I think you can do what you want as:

select row_number() over (order by (select NULL)) as seqnum,
       ComponentRef,
       (case when ComponentRef is not null then max(ComponentTitle)
             else cast(count(*) as varchar(255))
        end)
from table t
group by ComponentRef;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425311

SELECT  rowNo, componentRef,
        CASE WHEN componentRef IS NULL THEN 'UsersCount' + ucsum ELSE componentRef END
FROM    (
        SELECT  *,
                SUM(CASE WHEN componentRef IS NULL THEN CAST(REPLACE(componentTitle, 'UsersCount', '') AS INT) END) AS ucsum,
                ROW_NUMBER() OVER (PARTITION BY componentRef ORDER BY rowNum) rn
        ) q
WHERE   componentRef IS NOT NULL OR rn = 1

Upvotes: 1

Related Questions