CODEWITHSUNDEEP
sqlms-access
Naci
Naci

Reputation: 231

Join cross tab and select queries

I have a table I would like to query. The query should include two sub queries, therefore I think a join operation is needed but I am not sure. My table looks like this:

Region      Product Status     Date
Region 1    A       Inventory   7/20/2016
Region 2    A       Development 7/1/2016
Region 2    B       Inventory   7/4/2016
Region 2    B       Inventory   7/9/2016
Region 3    B       Development 7/8/2016
Region 3    C       Inventory   7/15/2016
Region 1    C       Development 7/10/2016

I would like to perform one query, so that it will first cross tabulate based on the product, then it will do another query (on two columns, where Status = Inventory and Date > 7/9/2016), and join the results to the results of the first query. In the end I would like the following result:

Region      A   B   C   Special Count
Region 1    1   0   1   1
Region 2    1   2   0   0
Region 3    0   1   1   1

What would be the code like for this purpose? Thank you

Note: I am using Access and I am not sure how standard Access SQL is compared to other SQL based software.

Upvotes: 1

Views: 531

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can do this with conditional aggregation:

select region,
       sum(iif(product = "A", 1, 0)) as A,
       sum(iif(product = "B", 1, 0)) as B,
       sum(iif(product = "C", 1, 0)) as C,
       sum(iif(status = "Inventory" and Date > #7/9/2016#, 1, 0)) as Special
from t
group by region;

Upvotes: 2

Related Questions