Add count() to pivot table with totals

I have the following query: (simplified to one year)

WITH myTable
     AS (SELECT cv.Company, 
                cv.Zip, 
                DATEPART(year, od.OrderDate) AS TheYear, 
                '' + DATEPART(year, od.OrderDate) AS TheYear2, 
                od.OrderNumber AS countOrders, 
                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 approved = 1
              AND cancelled = 0
              AND od.OrderDate > '01-JAN-2010')

–and the PIVOT

SELECT Company, 
        Zip, 
        Meetings, 
        [02020] AS [Orders - 2020], 
        ISNULL(CAST([2020] AS INT), 0) AS [Total - 2020], 
        Total = CAST((SUM(ISNULL([2020], 0))) AS INT)
 FROM myTable PIVOT(COUNT(countOrders) FOR TheYear2 IN([02020])) AS myPvt PIVOT(SUM(total) FOR TheYear IN([2020])) AS myPvt2
 GROUP BY Company, 
          zip, 
          Meetings, 
          [2020], 
          [02020];

For it I’m trying to add the count of orders for each year, but I’m having problems understanding how should I proceed, I might be complicating it too much, but I’m not getting the proper count I think that the myTable table is getting properly the “countOrders” but I’m not sure if I’m doing it properly on the pivot table to group by them and show the count by year

right now it kinda seems that is counting all the orders as I needed, however when I manually check on the DB seems that the data is off.

also, something I don’t understand is why is it repeating the “company” so many times when I’m doing grouping by it?

example

Answer

I ended up case instead of pivot table. this solved the issue, with format:

            CAST(SUM(CASE WHEN TheYear = 2010 THEN 1 ELSE 0 END)AS INT) AS [Orders - 2010] ,
            CAST(SUM(CASE WHEN TheYear = 2010 THEN Total ELSE 0 END)AS INT) AS [Total - 2010],

in case it works for someone 🙂

Advertisement

Answer