CODEWITHSUNDEEP
sqlexcelms-accessgroup-bycount
Sapna B
Sapna B

Reputation: 13

Count number of occurrences in category grouped by date

I have a list of data (~7000) lines that are marked by their week commencing dates. Each row of data has a certain category associated with it, I want to make a query or results table that takes each unique commencing week date and summarizes the counts for each category based on rows of data that have that week commencing date.

I have tried using a combination of access queries and excel modifications.

My current data looks something like:

Category    week
A               5/20/2019
B               5/20/2019
B               5/20/2019
C               5/20/2019
A               5/20/2019
A               5/13/2019
C               5/13/2019
C               5/13/2019
B               5/6/2019
A               5/6/2019
A               5/6/2019

I want something in the format of:

week              A      B      C
5/20/2019         2      2      1
5/13/2019         1      0      2
5/6/2019          2      1      0

whether its in multiple queries or whatever, I just need this data separated like this.

Upvotes: 1

Views: 1319

Answers (3)

Lee Mac
Lee Mac

Reputation: 16015

One possible method is using conditional aggregation, e.g.:

select 
    t.week,
    sum(iif(t.category = "A", 1, 0)) as A,
    sum(iif(t.category = "B", 1, 0)) as B,
    sum(iif(t.category = "C", 1, 0)) as C
from 
    YourTable t
group by
    t.week

If you wish to group multiple categories together to be listed as a single category, you can use an in expression in the test expressions for the iif statements:

select 
    t.week,
    sum(iif(t.category = "A", 1, 0)) as A,
    sum(iif(t.category = "B", 1, 0)) as B,
    sum(iif(t.category = "C", 1, 0)) as C,
    sum(iif(t.category in ("D","E","F"), 1, 0)) as D
from 
    YourTable t
group by
    t.week

Alternatively, you can use a crosstab query, e.g.:

transform count(*)
select t.week
from YourTable t
group by t.week
pivot t.category;

Change YourTable to the name of your table.

Upvotes: 2

Van Ng
Van Ng

Reputation: 803

Best way, to my mind, due you have mentioned MS Access in your tags is to use built-in methods:

    TRANSFORM Count(tblMyTableName.week) AS [Count-week]
    SELECT tblMyTableName.week
    FROM tblMyTableName
    GROUP BY tblMyTableName.week
    PIVOT tblMyTableName.Category;

Where tblMyTableName is name of your table.

Upvotes: 0

Error 1004
Error 1004

Reputation: 8220

You could use Pivot table:

Create a Pivot Table selecting all data (including header)

enter image description here

Upvotes: 2

Related Questions