CODEWITHSUNDEEP
sqlms-accessms-access-2016
HotTomales
HotTomales

Reputation: 564

Combine Two Queries To One

In Access - I'd like to show ONE query that provides the user with the results of BOTH of these queries so the query results are State, CountOfSP, CountOfUsers

Currently to get the information we run two separate queries of

Select State, Count(userID) As CountOfUsers
From SalesData
Group By State
Order By State;

Then our second query is

Select [%$##_Alias].State, Count(salesPerson) As CountOfSalesPerson
From 
(
  Select distinct salesPerson, State
  From salesData
) AS [%$##@_Alias]
Group By [%$##@_Alias].State

Is there a way in access sql to combine these two queries so I can get all the data I need form ONE query?

EDIT
I tried this syntax, but it gives me largely inflated numbers and the exact same count for CountOfUsers and CountOfSalesPerson

SELECT salesData.State, Count(salesData.userID) AS CountOfUsers, C 
ount(Regioninfo.salesPerson) AS CountOfSalesPerson
FROM salesData 
LEFT JOIN 
(
    Select distinct salesPerson, State
    From salesData
    group by Region, salesPerson
)  AS Regioninfo ON salesData.State = Regioninfo.State
GROUP BY salesData.State
ORDER BY salesData.State;

Upvotes: 0

Views: 55

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

You can use two levels of aggregation:

select State, sum(cnt) as CountOfUsers, count(*) as CountOfSalesPerson
from (Select State, salesPerson, Count(*) As cnt
      From SalesData
      Group By State
     ) as s
group by State
order by State;

Upvotes: 2

Hogan
Hogan

Reputation: 70513

you can join them like this:

Select State, Count(userID) As CountOfUsers, Count(stateinfo.salesPerson) As CountOfSalesPerson
From SalesData
left join (
  Select salesPerson, State
  From salesData
  group by salesperson, state
) AS stateinfo on salesdata.state = stateinfo.state
Group By Region
Order By Region;

another way:

Select State, Count(userID) As CountOfUsers, stateinfo.c As CountOfSalesPerson
From SalesData
left join (
  select count(z) as c, state
  from (
    Select distinct salesPerson as z, State
    From salesData
  ) as x
   group by state
) AS stateinfo on salesdata.state = stateinfo.state
Group By Region, stateinfo.c
Order By Region;

Upvotes: 1

Amrita Srivastava
Amrita Srivastava

Reputation: 384

;with t as
(
    Select [%$##_Alias].State, Count(salesPerson) As CountOfSalesPerson
    From 
    (
        Select distinct salesPerson, State
        From salesData
    ) AS [%$##@_Alias]
    Group By [%$##@_Alias].State
)

Select State, Count(userID) As CountOfUsers, t.CountOfSalesPerson
From SalesData s
left join t on t.state = s.state
Group By Region
Order By Region;

Upvotes: -2

Related Questions