piotr piotr - 1 month ago 11
SQL Question

Average count data from pivot table - Need assistance

I am working on a little project to deliver application that pull data from database to show average of shipment made each day of the week. I have made some progress and have now script that count all of shipment but because I count them based on varchar type of column also I need solution to calculate average for each day of the week separately.

So far I end up with something as follows:

SELECT [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM (
SELECT DATENAME(dw, Shipment.Date) AS DayWeek, Shipment.ID
FROM Shipment
WHERE MONTH(Shipment.Date)= MONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, 'OCTOBER 2016' ), 0)) AND
Shipment.Bur = 'GB'
) AS src
pivot (
COUNT(ID) FOR DayWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
) AS pvt


I appreciate much any hint or help as it is looks like I run out of ideas at the moment and can get any further to deliver right solution.

Answer

I found the answer for my question by browsing through stackoverflow encountering on similar problem posted by one of the user.

    SELECT [Day], SUM(q.Totals) AS "Weekly Shipment Amount", AVG(Totals) AS [Avg]
FROM
(
    SELECT 
  w = DATEDIFF(WEEK, 0, DATE),
      [Day]  = DATENAME(Weekday, DATE),
      Totals = COUNT(*)
    FROM dbo.Shipment
    --INNER JOIN FCLI (NOLOCK) ON FCLI.CCLI = FEXP.CCLI 
      WHERE (BUR = 'GB' AND
      Shipment.Date >= '20160601' AND Shipment.Date <= '20160630')

    GROUP BY 
        DATEDIFF(WEEK, 0, Date),
        DATENAME(WEEKDAY, Date),
        DATEPART(WEEKDAY, Date)
  ) AS q
GROUP BY [Day]
ORDER BY [DaY] ASC;
Comments