得到员工的总数与交易

Question

总数我有

employeeid, country, state, and city 

一个员工表以后，我的交易表

transaction_id, employeeid, transaction-details 

所以我需要

country state city total(no_of_employee), count(transactions), count(no_of_employee_done_transaction) 

我已经尝试并能够获得

country state city count(transactions), count(no_of_employee_done_transaction) 

通过使用此查询：

select em.Country, em.state, em.city , count(transaction_id) as "count(transaction)" , count(distinct(employeeid)) as "count(number of employee done transaction)" 
    from transaction tr 
    right outer join employee em on tr.employeeid = em.employeeid 
    where to_char(tran_date,'Mon-YYYY')='Jun-2014' 
    group by em.country,em.state, em.city; 

，如果我把count(employee_id)在选择列表中，然后它总是等于count(transaction_id) 什么修改上面的查询，实现count(employee_id)。

Answer 1

看来，在一个地方，您希望从员工表和第二位员工的总数中，您希望从交易表完成交易的员工。试试：

select em.Country, 
     em.state, 
     em.city , 
     count(distinct em.employeeid), 
     count(transaction_id) as "count(transaction)" , 
     count(distinct tr.employeeid) as "count(number of employee done transaction)" 
    from transaction tr 
    right outer join employee em on tr.employeeid = em.employeeid 
    where to_char(tran_date,'Mon-YYYY')='Jun-2014' 
    group by em.country,em.state, em.city; 

Answer 2

使用SQL Subquery。

select a.employeeid,a.country,a.state,a.city, 
(select count(*) from employees) as count_employees, 
(select count(*) from transactions) as count_transactions, 
(select count(*) from employees where employee_id=a.employee_id) as no_of_employee_transactions 
    from transactions a 
where to_char(a.tran_date,'Mon-YYYY')='Jun-2014' 
group by a.employeeid,a.country,a.state,a.city; 

Answer 3

不是很清楚您的要求。请看看这是你想要的。

select em.Country, em.state, em.city , count(transaction_id) as "count(transaction)" , count(distinct(employeeid)) as "count(number of employee done transaction)", 
(select count(distinct(employeeid)) as total(no_of_employee) 
    from employee c 
    where em.Country = c.Country 
      em.state = c.state 
      em.city = c.city) 
    from transaction tr 
    right outer join employee em on tr.employeeid = em.employeeid 
    where to_char(tran_date,'Mon-YYYY')='Jun-2014' 
    group by em.country,em.state, em.city;