Avatar of erikTsomik
erikTsomik
Flag 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
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
Jim Horn

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

ASKER
Yes I Need the total
Jim Horn

< slight correction to my first post.  To conditionally count using a CASE block it's SUM(WHEN expression THEN 1 ELSE 0 END >
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
erikTsomik

ASKER
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

Jim Horn

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

ASKER
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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mike Eghtebas

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
erikTsomik

ASKER
Are there any suggestions how to split the response by state
Mike Eghtebas

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
erikTsomik

ASKER
Great. Thank you
PortletPaul

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"
Jim Horn

Correct.  Good catch Paul.  -Jim
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.