covert month to quarter and sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

covert month to quarter and sum

Author

Topic

chaoys
Starting Member

2 Posts

Posted - 2010-08-03 : 14:37:28

Hi, all,

I try to covert month to quarter and sum up all the quarter sales from one table, however I keep getting compiling errors,

Table1:
FIPS year month sales
X 2003 1 50120
X 2003 1 30520
X 2003 1 49998

I would like my output to be,
FIPS year month sales
X 2003 1 130638

please find my codes below,

SELECT fips, year,
quar = CASE
WHEN month BETWEEN 1 and 3 THEN 1
WHEN month BETWEEN 4 and 6 THEN 2
WHEN month BETWEEN 7 and 9 THEN 3
WHEN month BETWEEN 10 and 12 THEN 4 END
, SUM(sales) AS sales
FROM table1
WHERE (year BETWEEN 2003 AND 2007)
GROUP BY fips, year, quar
ORDER BY fips, year, quar

Any thoughts?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-03 : 14:48:37

It's just in your group by clause, SQL doesn't recognize quar as a column yet, but notice that it does in the order by statement


ECLARE @Table1 TABLE (FIPS char(1),[year] int ,[month] int, sales int)
INSERT INTO @Table1
SELECT 'X', 2003, 1, 50120  UNION ALL
SELECT 'X', 2003, 1, 30520  UNION ALL
SELECT 'X', 2003, 1, 49998

 
SELECT fips, year, 
quar = CASE
WHEN month BETWEEN 1 and 3 THEN 1
WHEN month BETWEEN 4 and 6 THEN 2
WHEN month BETWEEN 7 and 9 THEN 3
WHEN month BETWEEN 10 and 12 THEN 4 END
, SUM(sales) AS sales
FROM @table1
WHERE (year BETWEEN 2003 AND 2007)
GROUP BY fips, year,  CASE
WHEN month BETWEEN 1 and 3 THEN 1
WHEN month BETWEEN 4 and 6 THEN 2
WHEN month BETWEEN 7 and 9 THEN 3
WHEN month BETWEEN 10 and 12 THEN 4 END
ORDER BY fips, year, quar

Jim

Everyday I learn something that somebody else already knew

chaoys
Starting Member

2 Posts

Posted - 2010-08-03 : 15:00:48

quote:

Originally posted by jimf

It's just in your group by clause, SQL doesn't recognize quar as a column yet, but notice that it does in the order by statement


ECLARE @Table1 TABLE (FIPS char(1),[year] int ,[month] int, sales int)
INSERT INTO @Table1
SELECT 'X', 2003, 1, 50120  UNION ALL
SELECT 'X', 2003, 1, 30520  UNION ALL
SELECT 'X', 2003, 1, 49998

 
SELECT fips, year, 
quar = CASE
WHEN month BETWEEN 1 and 3 THEN 1
WHEN month BETWEEN 4 and 6 THEN 2
WHEN month BETWEEN 7 and 9 THEN 3
WHEN month BETWEEN 10 and 12 THEN 4 END
, SUM(sales) AS sales
FROM @table1
WHERE (year BETWEEN 2003 AND 2007)
GROUP BY fips, year,  CASE
WHEN month BETWEEN 1 and 3 THEN 1
WHEN month BETWEEN 4 and 6 THEN 2
WHEN month BETWEEN 7 and 9 THEN 3
WHEN month BETWEEN 10 and 12 THEN 4 END
ORDER BY fips, year, quar

Jim

Everyday I learn something that somebody else already knew

Lifesaver, this is great, it worked!

Thank you very much, Jim!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-03 : 15:22:20

SELECT (MONTH - 1) / 3 + 1 AS Quarter

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources