Aggregate function within inner select in Pivot query using SQL Server

Question

I have the following table:

select * from product;

slno  item
---------------
1     HDD
2     PenDrive
3     RAM
4     DVD
5     RAM
6     HDD
7     RAM
7     RAM
7     RAM

Now I need to do pivoting for this table for which i am using following query:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(item) 
                from product
                group by item
                  order by item
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')



set @query = 'SELECT slno,TotalProduct ,' + @cols + ' 
        from 
        (
            select slno,Count(*) as TotalProduct,item
            from product
            group by slno,item
        ) x
        pivot 
        (
            count(item)
            for item in (' + @cols + ')
        ) p '

exec(@query)

Result:

 slno  TotalProducts  DVD  HDD  PenDrive  RAM
 ---------------------------------------------
 1         1           0    1      0       0
 2         1           0    0      1       0
 3         1           0    0      0       1
 4         1           1    0      0       0
 5         1           0    0      0       1
 6         1           0    1      0       0
 7         3           0    0      0       1

Note The total of product RAM is 3 but in Column RAM showing only 1. I have used COUNT(*) aggregate function within the inner select statement in @query. How can i show actual count?

shree.pat18 · Accepted Answer · 2014-08-25 06:44:01Z

1

You only need to group by slno, not by the combination of slno and item. Therefore, you need to change the query which provides a source for your pivot as follows:

set @query = 'SELECT slno,totalproduct,' + @cols + ' 
    from 
    (
        select p.slno slno, c.count as totalproduct, p.item
        from product p
        inner join 
        (select slno, count(item) count
         from product 
         group by slno) c on p.slno = c.slno
    ) x
    pivot 
    (
        count(item)
        for item in (' + @cols + ')
    ) p '

Demo

answered Aug 25, 2014 at 6:44

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mehdi lotfi · Accepted Answer · 2014-08-25 07:22:37Z

1

Use following sub query instead of your sub query:

select slno,Count(*) OVER (PARTITION BY slno) as TotalProduct,item
from product

Edit: Count(*) Over(Partition by ...) supported in SQL Server 2012 and above versions.

edited Aug 25, 2014 at 7:22

answered Aug 25, 2014 at 6:49

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

4 Comments

Sarfaraz Makandar Over a year ago

Have you checked your script before posting?

mehdi lotfi Over a year ago

Count(*) Over (Partition by) supported in SQL Server 2012 and above version.

mehdi lotfi Over a year ago

the result of my query completely like result of shree.pat sub query.

Sarfaraz Makandar Over a year ago

Oh! "Apologize" I am using 2008. Plz edit your answer so that I can up vote it.

Collectives™ on Stack Overflow

Aggregate function within inner select in Pivot query using SQL Server

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related