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]
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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.
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
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.
ASKER
Thank you.