How to pull certain data by month (Jan, Feb) from a Date table with the output having columns for each month.
-
I'm sorry if the Subject title of this sounds confusing, but I don't know how to describe what I need. Hopefully, the below tables and explanation will be enough info.
Sampling of the data from the Core database
Getting the January and February columns to both show up has been the biggest problem. I am by no means a SQL or Database expert and would greatly appreciate any help that I can get on this.
I am actually pulling the data from an Access database directly from Excel using Microsoft Query. Here is some of the code that I have come up with so far:
SELECT programName AS Program, CONVERT(int,Round(AVG(Score),0)) AS 'Feb Average Score'
FROM core
WHERE (Date>='2/1/2016’) and (Date<'3/1/2016') --This gets me February data
GROUP BY programName
ORDER BY programName
I use CONVERT and ROUND because I don’t want any decimal places. I also plan to do this for all 12 months, but for I believe once I get 2 months to work, I can figure out how to pull for all 12 months.
Thank you!
-
Update! - I just found out that the way that I am getting the average score isn't working correctly. It is not giving me the correct average score.
-
Check the following articles to learn how to use cross tabs, which will get you exactly what you need.
http://www.sqlservercentral.com/articles/T-SQL/63681/
This is SQL Server based code and it's not guaranteed to work with other systems. But it's quite simple to adjust if necessary.
SELECT programName AS Program
,AVG(CASE WHEN MONTH( Date) = 1 THEN Score END) AS JanAverageScore
,AVG(CASE WHEN MONTH( Date) = 2 THEN Score END) AS FebAverageScore
,AVG(CASE WHEN MONTH( Date) = 3 THEN Score END) AS MarAverageScore
FROM Core
GROUP BY programName
ORDER BY programName;
-
Thank you, Luis. This is helping me out greatly so far
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply