sql calculate sum

erikTsomik
erikTsomik used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.
erikTsomikSystem Architect, CF programmer

Author

Commented:
Yes I Need the total
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
< slight correction to my first post.  To conditionally count using a CASE block it's SUM(WHEN expression THEN 1 ELSE 0 END >
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Use SUM instead of COUNT .. corrected my first post.
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

Mike EghtebasDatabase and Application Developer

Commented:
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
erikTsomikSystem Architect, CF programmer

Author

Commented:
Are there any suggestions how to split the response by state
Mike EghtebasDatabase and Application Developer

Commented:
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.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>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

erikTsomikSystem Architect, CF programmer

Author

Commented:
Great. Thank you
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Correct.  Good catch Paul.  -Jim

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial