Link to home
Get AccessLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>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.
Avatar of erikTsomik

ASKER

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'

Open 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

Open 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

Open 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.
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
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