group by a calculated days delinquent field

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

General SQL Server Forums

New to SQL Server Programming

group by a calculated days delinquent field

Author

Topic

brodadam
Starting Member

1 Post

Posted - 2012-10-30 : 12:38:31

I am very new to SQL and am trying to create a query based off of 1 table so that I can group by a calculated field. Example:

SELECT DATEDIFF(DAY,INV_DUE_DATE,DATE_OF_PYM) as DDL, CREDIT_CLASS, sum(convert(money,AMT_PAID)) as TOT_AMT_PAID
FROM PaymentData2
WHERE DATE_OF_PYM like '%SEP%'
Group By DDL, CREDIT_CLASS
Order by DDL

In the end i would like to group by DDL (Days Delinquent), however this code errors out. Any suggestions on how I can get there?

I had to run the convert function as all fields were imported as varchar(255).

I really appreciate any help. Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-30 : 12:58:46

Yes you can't refer to a column alias in any part of your query except the order by, so just a little tweak

SELECT DATEDIFF(DAY,INV_DUE_DATE,DATE_OF_PYM) as DDL, CREDIT_CLASS, sum(convert(money,AMT_PAID)) as TOT_AMT_PAID
FROM PaymentData2
WHERE DATE_OF_PYM like '%SEP%' --- no ddl here
Group DATEDIFF(DAY,INV_DUE_DATE,DATE_OF_PYM) , CREDIT_CLASS -- no DDL here
Order by DDL -- but her it's okay

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 13:39:05

Another way is by forming a derived table as below. But I wont recommend it unless the expression you've is really complicated


SELECT DDL, CREDIT_CLASS,sum(convert(money,AMT_PAID)) as TOT_AMT_PAID
FROM
(
SELECT DATEDIFF(DAY,INV_DUE_DATE,DATE_OF_PYM) as DDL, CREDIT_CLASS, AMT_PAID
FROM PaymentData2
WHERE DATE_OF_PYM like '%SEP%'
)t
Group By DDL, CREDIT_CLASS
Order by DDL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources