SQL Pivot sum with specific condition

Question

I am trying to create a pivot in order to sum specific month as the total, below is my table and sql pivot:

CREATE TABLE yt
([Store] varchar(10), [Month] varchar(10), [xCount] int)
;

INSERT INTO yt
([Store], [Month], [xCount])
VALUES
('A', 'JAN', 1),
('A', 'FEB', 10),
('B', 'JAN', 2),
('B', 'FEB', 20),
('C', 'JAN', 3),
('C', 'FEB', 70),
('C', 'MAR', 110)    
;

    select *
    from 
    (
      select Store, Month, xCount
      from yt
    ) src
    pivot
    (
      sum(xcount)
      for Month in ([JAN], [FEB], [MAR])
    ) piv;

Store   JAN FEB MAR
A        1  10  NULL
B        2  20  NULL
C        3  70  110

The above result I am able to sum the value by the current month. Now I want to sum all the month when Store a, sum only FEB, and MAR in Store b, and Store c sum MAR, so I want my result as

Store   JAN FEB MAR
A        6  100 110
B        5  90  110
C        3  70  110

Can I do it in pivot? Or need to create a table to run sql again? Thanks for the help.

score 2 · Answer 1 · answered Oct 21 '15 at 08:09

For Sql Server 2008 you need some self join and ordering to make a running sum. Here I take store as this ordering:

WITH cte AS (SELECT * FROM @yt 
             PIVOT(SUM(xcount) FOR Month IN ([JAN], [FEB], [MAR])) piv)
SELECT  c1.Store ,
        SUM(c2.jan) AS jan ,
        SUM(c2.feb) AS feb ,
        SUM(c2.mar) AS mar
FROM    cte c1
        JOIN cte c2 ON c1.Store <= c2.Store
GROUP BY c1.Store

score 1 · Answer 2 · edited May 23 '17 at 12:14

1

Your problem is running total order by store desc In SQL Server2005+, You can try this:

SELECT Store,
    SUM([JAN]) OVER(ORDER BY Store DESC) AS [JAN],
    SUM([FEB]) OVER(ORDER BY Store DESC) AS [FEB],
    SUM([MAR]) OVER(ORDER BY Store DESC) AS [MAR]
FROM (
SELECT *
    FROM
    (
      SELECT Store, Month, xCount
      FROM yt
    ) src
    PIVOT
    (
      SUM(xcount)
      for Month in ([JAN], [FEB], [MAR])
    ) piv
) AS A
ORDER BY Store

But in MySQL, you can reference this link

edited May 23 '17 at 12:14

Community

1
1

answered Oct 21 '15 at 07:53

Nguyễn Hải Triều

1,454
1
8
14

Thanks for your reply, but I run my sql get error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'order'. Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'AS'. – Arzozeus Oct 21 '15 at 07:58
Oh! My code's used SQL 2005+ (use Window function) – Nguyễn Hải Triều Oct 21 '15 at 07:59
Oh, why your tag: sql-server? I mean, my code in SQL Server :) – Nguyễn Hải Triều Oct 21 '15 at 08:02
1

sum with order by is available from `sql server 2012` – Giorgi Nakeuri Oct 21 '15 at 08:11
Thanks for your help @NguyễnHảiTriều Hải Triều – Arzozeus Oct 21 '15 at 08:12
@Arzozeus - There is no MySQL 2008. Based on your code and error, you are using SQL Server 2008 – ughai Oct 21 '15 at 08:53
@ughai: In first time, he put his tag: sql-server. Then, he fixed it. – Nguyễn Hải Triều Oct 21 '15 at 08:54
use of `[` to surround a table name is not supported in MySQL which uses ticks (`) – ughai Oct 21 '15 at 08:56

score 0 · Answer 3 · answered Oct 21 '15 at 08:46

If you are using SQL SERVER 2012, you can use

SELECT STORE,
       SUM(JAN)
         OVER (
           ORDER BY RN DESC) JAN,
       SUM(FEB)
         OVER (
           ORDER BY RN DESC) FEB,
       SUM(MAR)
         OVER (
           ORDER BY RN DESC) MAR
FROM  (SELECT ROW_NUMBER()
                OVER (
                  ORDER BY STORE)RN,
              *
       FROM   (SELECT STORE,
                      MONTH,
                      XCOUNT
               FROM   #YT) SRC
              PIVOT ( SUM(XCOUNT)
                    FOR MONTH IN ([JAN],
                                  [FEB],
                                  [MAR]) ) PIV)A 
ORDER BY RN ASC

thanks for your help, if i run in 2008 will show error Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'order'. Msg 102, Level 15, State 1, Line 23 Incorrect syntax near 'A'. it seems 2008 not support over method, i guess — Arzozeus, Oct 21 '15 at 08:57

SQL Pivot sum with specific condition

3 Answers3