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!
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
THe table structure is simple
Car:
carID
dateadded
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
>I need to calculate sum of cars
By any chance do you mean count 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
< 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'
can i also segregate by state . The column is availStateKey
if availStateKey = 1 or 3 maryland, availStateKey = 2 -virginia,availstateKey = 4 california
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
Also try:
replace #w with your table name. Revised
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
replace #w with your table name. Revised
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.
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.
>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.
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
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"
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"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial