sql calculate sum

I need to calculate sum of cars that are less than 12 month old, 12-24 month old , 24+ month old

THe table structure is simple

Car:
carID
dateadded
LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I need to calculate sum of cars
By any chance do you mean count of cars? 
Declare @dt datetime = GETDATE()
SELECT 
   SUM(CASE WHEN dateadded >= DATEADD(month, -12, @dt) AND dateadded < @dt THEN 1 ELSE 0 END) as less_than_12_months_old, 
   SUM(CASE WHEN dateadded >= DATEADD(month, -24, @dt) AND dateadded < DATEADD(month, -12, @dt) THEN 1 ELSE 0 END) as between_12_and_24months_old, 
   SUM(CASE WHEN dateadded < DATEADD(month, -24, @dt) THEN 1 ELSE 0 END) as older_than_24_months_old
FROM Car

Open in new window

This methodology and other things to do with CASE is illustrated in my EE article SQL Server CASE Solutions.
0
erikTsomikSystem Architect, CF programmer Author Commented:
Yes I Need the total
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
< slight correction to my first post.  To conditionally count using a CASE block it's SUM(WHEN expression THEN 1 ELSE 0 END >
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
erikTsomikSystem Architect, CF programmer Author Commented:
I am getting the same number for all 3 ranges 

Declare @dt datetime = GETDATE()


SELECT 
	COUNT (CASE WHEN SM.dateadded >= DATEADD(month, -12, @dt) AND SM.dateadded < @dt THEN 1 ELSE 0 END) as less_than_12_months_old, 
   COUNT (CASE WHEN SM.dateadded >= DATEADD(month, -24, @dt) AND SM.dateadded < DATEADD(month, -12, @dt) THEN 1 ELSE 0 END) as between_12_and_24months_old, 
   COUNT (CASE WHEN SM.dateadded < DATEADD(month, -24, @dt) THEN 1 ELSE 0 END) as older_than_24_months_old
	from SessionMap SM
	inner join orders O on O.orderKey = SM.orderKey
	where (SM.sessionKey = 0 OR  SM.userKey  = 0) and SM.type = 'BW'  
	--and O.orderDate > DATEADD(MONTH,-12,GETDATE())
	AND O.void <> 'Y'

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Use SUM instead of COUNT .. corrected my first post.
0
erikTsomikSystem Architect, CF programmer Author Commented:
can i also segregate by state . The column is availStateKey

if availStateKey = 1 or 3  maryland, availStateKey = 2 -virginia,availstateKey = 4 california

Declare @dt datetime = GETDATE()


SELECT 
	SUM (CASE WHEN SM.dateadded >= DATEADD(month, -12, @dt) AND SM.dateadded < @dt THEN 1 ELSE 0 END) as less_than_12_months_old, 
   SUM (CASE WHEN SM.dateadded >= DATEADD(month, -24, @dt) AND SM.dateadded < DATEADD(month, -12, @dt) THEN 1 ELSE 0 END) as between_12_and_24months_old, 
   SUM (CASE WHEN SM.dateadded < DATEADD(month, -24, @dt) THEN 1 ELSE 0 END) as older_than_24_months_old
   
	from SessionMap SM
	inner join orders O on O.orderKey = SM.orderKey
	where (SM.sessionKey = 0 OR  SM.userKey  = 0) and (SM.type = 'BW' or SM.type = 'SBW')  
	--and O.orderDate > DATEADD(MONTH,-12,GETDATE())
	AND O.void <> 'Y'
	GROUP BY O.availStateKey

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
Also try: 
Select HowOld, count(HowOld) As Qty
From (Select 
	  Case 
	   when datediff(Month, dateadded, getdate()) < 12 then '<12'
	   when datediff(Month, dateadded, getdate()) between 12 and 24 then '12-24'
	   else '>24' End As HowOld
      From #w) As D (HowOld) 
Group By HowOld

Open in new window


replace #w with your table name.  Revised
0
erikTsomikSystem Architect, CF programmer Author Commented:
Are there any suggestions how to split the response by state
0
Mike EghtebasDatabase and Application DeveloperCommented:
please revise this table to include state data. Or, provide a better sample data:
carID  dateAdded
1      2015-10-01
2      2014-06-01
3      2012-08-01
4      2012-08-01


will respond in a few hours unless someone else beats me to it.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Are there any suggestions how to split the response by state
Two responses:

1  We've already answered your question as asked, so now we're answering new requirements as a follow-on question, and there's a limit to how engaged experts will be, especially if we start wondering how many more follow-on questions there are.  For a humorous example see  ​Top 10 Ways to Ask Better Questions, Number Three

2  Subquery to handle the state logic, then build on the first solution for the SUM's. 
Declare @dt datetime = GETDATE()

SELECT a.state_code,  
   SUM (CASE WHEN a.dateadded >= DATEADD(month, -12, @dt) AND a.dateadded < @dt THEN 1 ELSE 0 END) as less_than_12_months_old, 
   SUM (CASE WHEN a.dateadded >= DATEADD(month, -24, @dt) AND a.dateadded < DATEADD(month, -12, @dt) THEN 1 ELSE 0 END) as between_12_and_24months_old, 
   SUM (CASE WHEN a.dateadded < DATEADD(month, -24, @dt) THEN 1 ELSE 0 END) as older_than_24_months_old
 FROM (
   SELECT CASE availStateKey 
      WHEN 1 THEN 'MD' WHEN 2 THEN 'VA' 
      WHEN 3 THEN 'MD' WHEN 4 THEN 'CA' END as state_code, 
   dateadded
   FROM Cars) a
GROUP BY a.state_code
ORDER BY a.state_code

Open in new window

0

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
erikTsomikSystem Architect, CF programmer Author Commented:
Great. Thank you
0
PortletPaulfreelancerCommented:
I would just like to clarify something.

If you want to count something conditionally you can use COUNT()

This would be accurate:
COUNT (
      CASE
            WHEN SM.dateadded >= DATEADD(month, -12, @dt) AND SM.dateadded < @dt
            THEN 1
      END
) as less_than_12_months_old

This however would NOT be accurate:
COUNT (
      CASE
            WHEN SM.dateadded >= DATEADD(month, -12, @dt) AND SM.dateadded < @dt
            THEN 1
            ELSE 0
      END
) as less_than_12_months_old

The aggregate function COUNT() increments for EVERY NON-NULL value.

So, when using COUNT()
do NOT include "else some-value-here"
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Correct.  Good catch Paul.  -Jim
0
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.