how can I count all row and count group by on one tables

Touchy Vivace Published at Dev

Touchy Vivace

i write a SQL Code like this

    SELECT count(tn.[ProductName])  as ProductCount,tn.[CategoryID],tp.estbProducerID,tp.NewProducer
from [tpdcn] tp,[tpdtn] tn
left join [tpdcn]
on tn.parentid = tpdcn.libDocumentID
where tp.libdocumentID = @id
group by tn.[CategoryID],tp.estbProducerID

it's show

   Product Count    CategoryID     estbProducerID
        2               1            810600017   
        9               2            810600017  
        2               3            810600017  
        2               4            810600017  
        1               5            810600017

but I need more one field to show like this

 Product Count  CategoryID     estbProducerID     Product Count All   
        2               1            810600017                16
        9               2            810600017  
        2               3            810600017  
        2               4            810600017  
        1               5            810600017

What should I do to make it in one table

Mikael Eriksson

You can use OVER Clause (Transact-SQL).

count(*) over() as [Product Count All]

It will count the rows using the specified partition. Without a partition clause it count all rows.

select count(T.[ProductName]) as ProductCount,
       T.CategoryID,
       T.estbProducerID,
       T.NewProducer,
       T.[Product Count All]
from (
     select tn.[ProductName],
            tn.[CategoryID],
            tp.estbProducerID,
            tp.NewProducer,
            count(*) over() as [Product Count All]
     from [tpdcn] tp,[tpdtn] tn
       left join [tpdcn]
         on tn.parentid = tpdcn.libDocumentID
     where tp.libdocumentID = @id
     ) as T
group by T.[CategoryID],
         T.estbProducerID

Collected from the Internet

Please contact [email protected] to delete if infringement.