CODEWITHSUNDEEP
sqlms-access
user1499261
user1499261

Reputation: 1

Access SQL Query, Returning 6 Counts from 1 Table Based on Dates

Sorry if this question has been asked before, my database table is structured like this:

AgentName | Sold1 | SoldDate1 | Sold2 | SoldDate2 | Sold3 | SoldDate3
Geoff     | True  | 20/06/2012 | False | Null     | True  | 20/04/2012
Alex      | True  | 13/02/2012 | True  | 26/06/2012 | False | Null

Basically what I am trying to do is to count how many Geoff has sold in Sold1 for the last month but first it must check if sold1 is true, then the same again for Sold2 and Sold3.

My aim is to have the returned data display grouped by AgentName and displayed as:

AgentName     TotalSold1       TotalSold2      TotalSold3

I hope someone can help as I have spent all day trying to find a solution :(

Upvotes: 0

Views: 84

Answers (1)

Nahuel Fouilleul
Nahuel Fouilleul

Reputation: 19305

table_name is the name of the table

select t0.AgentName, CountSold1, CountSold2, CountSold3 from (
  select distinct AgentName from table_name
) t0 left join (
  select AgentName, count(*) CountSold1 from table_name
  where Sold1 = 'True' and SoldDate1 between '01-JUN-2012' and '30-JUN-2012'
  group by AgentName
) t1 on t0.AgentName = t1.AgentName left join (
  select AgentName, count(*) CountSold2 from table_name
  where Sold2 = 'True' and SoldDate2 between '01-JUN-2012' and '30-JUN-2012'
  group by AgentName
) t2 on t1.AgentName = t2.AgentName left join (
  select AgentName, count(*) CountSold3 from table_name
  where Sold3 = 'True' and SoldDate3 between '01-JUN-2012' and '30-JUN-2012'
  group by AgentName
) t3 on t2.AgentName = t3.AgentName

Upvotes: 1

Related Questions