CODEWITHSUNDEEP
sql-servert-sql
nnmmss
nnmmss

Reputation: 2974

Getting the Count of records without GROUP BY

Suppose I have a table like this

Id  TagNo  F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 F11 CoreNo
 1   1000   A  B  C  D  E  F  R  E W   W   D    1
 2   1000   A  B  C  D  E  F  R  E W   W   D    2
 3   1000   A  B  C  D  E  F  R  E W   W   D    3
 4   1001   A  B  C  D  E  F  R  E W   W   D    1
 5   1001   A  B  C  D  E  F  R  E W   W   D    2

I want to have 

  TagNo  F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 F11 CoreNo TotalCores
   1000   A  B  C  D  E  F  R  E W   W   D    1       3
   1000   A  B  C  D  E  F  R  E W   W   D    2
   1000   A  B  C  D  E  F  R  E W   W   D    3
   1001   A  B  C  D  E  F  R  E W   W   D    1       2
   1001   A  B  C  D  E  F  R  E W   W   D    2

The value for columns F1... F11 (which actually are more than that) are not important.

I know about Count(). But if I want to use Count() then I have to group by all the columns, which I don't want .

I hope that I could make it clear

Thank you

Upvotes: 0

Views: 8309

Answers (3)

msi77
msi77

Reputation: 1632

Without GROUP BY and COUNT :-)

select *, 
  (select max(coreno) from table t2 where t1.tagno=t2.tagno and t1.coreno=1) TotalCores 
from table t1

Upvotes: -1

marc_s
marc_s

Reputation: 754348

You could use this:

SELECT
    TagNo, F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, CoreNo,
    TotalCores = COUNT(*) OVER(PARTITION BY TagNo)
FROM 
    dbo.YourTable

Upvotes: 5

James Sefton
James Sefton

Reputation: 133

Maybe do a sub-select with group by in the sub select. Not a great idea though if its a big table.

Upvotes: 0

Related Questions