CODEWITHSUNDEEP
sqlsql-servergroup-bycountpivot
jfiggins
jfiggins

Reputation: 129

Is there a way to split sql results by year?

I currently have the following SQL statement:

SELECT
    [Manager].[Name], 
    COUNT([Project].[ProjectId]) AS TotalProjects
FROM 
    ([Project]
INNER JOIN 
    [Manager] ON [Project].[ManagerId] = [Manager].[ManagerId])
WHERE 
    [Project].[CurrentStatusId] = 5
GROUP BY 
    [Manager].[Name]

It currently spits out total projects by each manager. I would like to have it split the projects out by the years they were completed. So basically count the total projects for each manager for each year (2016, 2017, and so on), as well as the total projects all time. I can use the column [Project].[CurrentStatusDt] for the date.

Upvotes: 0

Views: 1082

Answers (1)

GMB
GMB

Reputation: 222672

Just add the project year to the SELECT and GROUP BY clauses:

SELECT
    [Manager].[Name], 
    YEAR([Project].[CurrentStatusDt]) PojectYear,
    COUNT([Project].[ProjectId]) AS TotalProjects
FROM [Project]
INNER JOIN [Manager] ON [Project].[ManagerId] = [Manager].[ManagerId]
WHERE [Project].[CurrentStatusId] = 5
GROUP BY [Manager].[Name], YEAR([Project].[CurrentStatusDt])

Side note: you don't need parentheses around the joins in SQL Server (this is a MS Access limitation).

EDIT

If you want to spread the years over columns instead of rows, then one solution is to use conditional aggregation

SELECT
    [Manager].[Name], 
    SUM(CASE 
        WHEN [Project].[CurrentStatusDt] < CAST('2019-01-01' AS DATE)
        THEN 1 
        ELSE 0 
    END) TotalProjects2018,
    SUM(CASE 
        WHEN [Project].[CurrentStatusDt] >= CAST('2019-01-01' AS DATE)
        THEN 1 
        ELSE 0 
    END) TotalProjects2019
FROM [Project]
INNER JOIN [Manager] ON [Project].[ManagerId] = [Manager].[ManagerId]
WHERE 
    [Project].[CurrentStatusId] = 5 
    AND [Project].[CurrentStatusDt] >= CAST('2018-01-01' AS DATE)
    AND [Project].[CurrentStatusDt] <  CAST('2020-01-01' AS DATE)
GROUP BY [Manager].[Name]

Upvotes: 6

Related Questions