May 22, 2016 at 11:53 am
Hi,
I'm having trouble finding my way out of a query that is supposed to retrieve the percentage of a count per each month of the year (jan to dec).
My query returns the percentage using the whole spectrum of records for the whole year instead of focusing of a monthly basis.
SELECT
wt.WorkTypeID,
MONTH(w.DateCreated) AS createdMonth,
CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS DECIMAL(18,2)) AS Percentages
FROM Works w
INNER JOIN MatterTypes mt ON mt.MatterTypeID = w.MatterTypeID
INNER JOIN WorkTypes wt ON wt.WorkTypeID = mt.WorkTypeID
WHERE YEAR(w.DateCreated) = 2016
GROUP BY
wt.WorkTypeID, MONTH(w.DateCreated)
Data coming from this query goes like:
WorkTypeIDcreatedMonthPercentages
1 1 0.96
2 1 1.60
3 1 0.96
4 1 0.64
5 1 0.96
6 1 0.96
1 2 2.24
2 2 1.92
3 2 2.24
4 2 0.96
5 2 0.32
6 2 0.64
1 3 0.96
2 3 2.24
3 3 2.24
4 3 2.56
5 3 1.28
[...]
How can I get the monthly total without having to apply a month filter and repeating the whole thing 12 times (figured that's not the way... =) )
May 22, 2016 at 8:30 pm
I guess you need to express your intentions inside of "OVER()" clause in your query.
If you want to do counting not over the whole period but over its parts than it probably makes sense to use a keyword resembling the word "part" somehow.
🙂
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply