SQL results by quarters and by year

Hello Experts;

I need to get counts by quarters and by years and I’m having a serious brain fart this afternoon.  Below is my results so far and it’s not working (my brain nor the query).  What I need is the count of records having a COMPLETION_DATE broken down by quarters and by year.  Something like below, but it does not have to be identical.  As long as it get the point across.

Year      Quarters        Total
2014      q1 = 200
               q2 = 100
               q3 = 100
               q4 = 100          500
2015 …..

By the way, when I run this query in 2008r2 I receive the following error:
Invalid column name 'quarter'.

SELECT
  CASE
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 1 AND 3   THEN 'Q1'
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 4 AND 6   THEN 'Q2'
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 7 AND 9   THEN 'Q3'
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 10 AND 12 THEN 'Q4'
  END AS [quarter],
  YEAR(a.COMPLETION_DATE) AS [year],
  COUNT(*)
FROM dbo.History a
WHERE
  ID = '5192714'
GROUP BY
  [quarter],
  [year]
wdbatesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
I think the first thing you should do is to change the group by clause to group by year, quarter.
Secondly, please use a subquery to do that:
SELECT [year], [quarter], count(*) as cnt 
from
(  select
  CASE 
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 1 AND 3   THEN 'Q1'
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 4 AND 6   THEN 'Q2'
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 7 AND 9   THEN 'Q3'
    WHEN MONTH(a.COMPLETION_DATE) BETWEEN 10 AND 12 THEN 'Q4'
  END AS [quarter],
  YEAR(a.COMPLETION_DATE) AS [year]
FROM dbo.History a
WHERE
  ID = '5192714') x
GROUP BY
  [year], 
  [quarter]

Open in new window

Then, see if the results are ok.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wdbatesAuthor Commented:
Great quick response.

Thank you.
PortletPaulEE Topic AdvisorCommented:
The reason you got that error message is because SQL does NOT perform the select clause first

Unlike the written sequence, SQL clauses are executed in this sequence:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

so your query looks like this when being executed:

FROM dbo.History a
WHERE
  ID = '5192714'
GROUP BY
  [quarter],
  [year]

ooops, what is [year] and [quarter] ? don't know, so send an error

By using a "derived table" in Walter's solution you solve that problem because the aliases [year] & [quarter] now do exist before the group by clause is executed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.