sql calculate sum

>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

Select allOpen in new window

This methodology and other things to do with CASE is illustrated in my EE article SQL Server CASE Solutions.

Yes I Need the total

< slight correction to my first post.  To conditionally count using a CASE block it's SUM(WHEN expression THEN 1 ELSE 0 END >

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'

Select allOpen in new window

Use SUM instead of COUNT .. corrected my first post.

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

Select allOpen in new window

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

Select allOpen in new window

replace #w with your table name.  Revised

Are there any suggestions how to split the response by state

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.

Great. Thank you

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"

Correct.  Good catch Paul.  -Jim