• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Advanced Sql query

Greetings,
I have a table like such:

Year       Type    
1990        A
1990        A
1990        B
1991        A
1991        B
1992        A
1992        A
1992       B

What I would like to do is write a query that will give me the additive cumulative values per year.  So the output might look like this:

Year         ACount        BCount
1990           2                      1
1991           3                      2
1992           5                      3


I hope this is clear enough:)  Thanks in advance!
0
ankykele
Asked:
ankykele
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select year
, sum(case when Type = 'A' then 1 else 0 end) Acount
, sum(case when Type = 'B' then 1 else 0 end) Bcount
from yourtable
 group by year
0
 
ankykeleAuthor Commented:
Thanks for the quick reply.  The query you provided only gives the cumulative amount for each year.  I would like a 'rolling' cumulative amount per year, retreiving TypeA and TypeB as an additive function.

Thanks in advance.


0
 
SharathData EngineerCommented:
What is your sql server version?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
SharathData EngineerCommented:
you can try this.
select [YEAR]
, sum(case when [Type] = 'A' then 1 else 0 end) Acount
, sum(case when [Type] = 'B' then 1 else 0 end) Bcount,
(select COUNT(*) from your_table t2 where [Type] = 'A' and t2.[Year]<= t1.[Year]) CumACount,
(select COUNT(*) from your_table t2 where [Type] = 'B' and t2.[Year]<= t1.[Year]) CumBCount
from your_table t1
 group by [YEAR]

Open in new window

0
 
ankykeleAuthor Commented:
SQL version is 2005
0
 
ankykeleAuthor Commented:
Thanks alot! It works perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now