0

so, I have a pivot table in which I have orders and they're grouped by year, I want to also count all the orders by year. I'm having a hard time doing this. this is the query:

DECLARE @fromDate AS DATETIME= '01-JAN-2016';
WITH myTable
     AS (SELECT cv.Company, 
                cv.Zip, 
                RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) AS Domain, 
                cv.EmailDomain, 
                DATEPART(year, od.OrderDate) AS TheYear, 
                FORMAT(cv.DateVisited, 'MM-yyyy') AS [DateVisited], 
                cv.MeetingType, 
                STUFF(
         (
             SELECT '| ' + FORMAT(DateVisited, 'MM-yyyy') + ' ' + MeetingType + ' '
             FROM CustomerVisits cv1
             WHERE(RIGHT(od.Email, LEN(od.Email) - CHARINDEX('@', od.email))) = cv1.EmailDomain
                  AND cv1.Zip = od.Zip
             GROUP BY MeetingType, 
                      FORMAT(DateVisited, 'MM-yyyy')
             ORDER BY FORMAT(DateVisited, 'MM-yyyy') ASC FOR XML PATH('')
         ), 1, 1, '') AS Meetings, 
                od.FinalProductTotal AS total
         FROM orders od
              LEFT JOIN CustomerVisits cv ON od.Zip = cv.Zip
         WHERE(RIGHT(od.Email, LEN(od.Email) - CHARINDEX('@', od.email))) = cv.EmailDomain
              AND od.OrderDate > @fromDate
         --  AND od.FinalProductTotal <> 0
         )


     SELECT Company, 
            Zip, 
            Meetings, 
            COUNT([2016]) AS [Orders - 2016], 
            ISNULL(CAST([2016] AS INT), 0) AS [Total - 2016], 
            COUNT([2017]) AS [Orders - 2016], 
            ISNULL(CAST([2017] AS INT), 0) AS [Total - 2017], 
            COUNT([2018]) AS [Orders - 2016], 
            ISNULL(CAST([2018] AS INT), 0) AS [Total - 2018], 
            COUNT([2019]) AS [Orders - 2016], 
            ISNULL(CAST([2019] AS INT), 0) AS [Total - 2019], 
            COUNT([2020]) AS [Orders - 2016], 
            ISNULL(CAST([2020] AS INT), 0) AS [Total - 2020], 
            Total = CAST((SUM(ISNULL([2016], 0) + ISNULL([2017], 0) + ISNULL([2018], 0) + ISNULL([2019], 0) + ISNULL([2020], 0))) AS INT)
     FROM myTable PIVOT(SUM(total) FOR TheYear IN([2016], 
                                                  [2017], 
                                                  [2018], 
                                                  [2019], 
                                                  [2020])) AS myPvt
     GROUP BY Company, 
              zip, 
              domain, 
              EmailDomain, 
              meetings, 
              [2016], 
              [2017], 
              [2018], 
              [2019], 
              [2020]
     ORDER BY Company;

and this is what is producing:

enter image description here

however, the count of "orders" is not working, I read about adding two pivots but I also wasn't able to make it work with it. any help? I've been reading about pivots and I can't seem to get this right

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.