piotr - 1 year ago 80
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.

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;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download