Query to sum values if in range

Dear all,

I have a table with the following fields, note construction year is a number.  

SuburbName         ConstructionYear    Size
SuburbA                 1980                          100
SurburbA               1989                           200
SuburbB                 2000                           300
SuburbB                 2009                         400  

I would like to sum up the size for the Construction Year in a range.  See the table below for the expected output.  

Tried sumif but couldn't get it to work.  The ranges for example are:
>=1980 AND <1990
>=1990 AND <2000

Expected output:
SuburbName    ConstructionYear    TotalSize
SuburbA             >=1980 AND <1990             300
SuburbB             >=1990 AND <2000             700

Thanks
AndyC1000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try use union, like:

Select SuburbName, '>=1980 AND <1990' as ConstructionYear, sum(Size)  as TotalSize
from yourTable
where ConstructionYear  >=1980 AND ConstructionYear   <1990
Group By SuburbName
union
Select SuburbName, '>=1990 AND <2000 ' as ConstructionYear, sum(Size)  as TotalSize
from yourTable
where ConstructionYear  >=1990 AND ConstructionYear  <2000 
Group By SuburbName
order by 1, 2

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
Try this query, assuming Table1. Second condition may be >=1990

SELECT SuburbName, grp As ConstructionYear, Sum(Size) as TotalSize 
from (SELECT Table1.SuburbName, Table1.ConstructionYear, Switch([ConstructionYear]>=1980 And [ConstructionYear]<1990,">=1980 AND <1990", [ConstructionYear]>=1990 And  [ConstructionYear],">=1990") AS grp, Table1.Size
FROM Table1)
GROUP By SuburbName, grp

Open in new window

Result:
SuburbName       ConstructionYear       TotalSize
SuburbA                 >=1980 AND <1990      300
SuburbB                  >=1990                          700
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.