Adding Group Columns

[luvmy2kids]

I have the below query:


DECLARE @StartDate datetime
SET @StartDate = '2009-7-27'
DECLARE @EndDate datetime
SET @EndDate = '2009-8-02'

select count (distinct employeeid), busunitdesc
from invinvoicedetail inv
inner join invinvoiceheader b on b.invoiceid = inv.invoiceid
inner join ssbusunit u on u.busunitid = inv.busunit
where b.invoicedate between @StartDate and @EndDate Group by busunitdesc

Which returns these results:

498 HR Solutions
1020 HR Solutions RTI

My ultimate goal is to get the total to add up to 1,518? If I do a distinct count without grouping, it returns 1,513 because there are 5 employees that are part of both groupings. How can I get a sum of 1,518?

Thanks a bunch

 

[RiverGuy]

Something like the following?

select count (distinct employeeid), busunitdesc
from invinvoicedetail inv
inner join invinvoiceheader b on b.invoiceid = inv.invoiceid
inner join ssbusunit u on u.busunitid = inv.busunit
where b.invoicedate between @StartDate and @EndDate Group by busunitdesc

UNION ALL


select count (distinct employeeid), 'Total' AS busunitdesc
from invinvoicedetail inv
inner join invinvoiceheader b on b.invoiceid = inv.invoiceid
inner join ssbusunit u on u.busunitid = inv.busunit
where b.invoicedate between @StartDate and @EndDate

 

[luvmy2kids]

That doesn't give me the correct answer of 1518, it still gives me 1513 because the total is counting distinct employeeid?

 

[markros]

Remove Distinct then and just do select count(*) as Total_Employees

 

[bborissov]

What you want as a result?

DECLARE @StartDate datetime,
        @EndDate datetime
SET @StartDate = '20090727'
SET @EndDate   = '20090802'

SELECT SUM(DistCount) AS DistCount
FROM(
select count (distinct employeeid) DistCount, busunitdesc
from invinvoicedetail inv
inner join invinvoiceheader b on b.invoiceid = inv.invoiceid
inner join ssbusunit u on u.busunitid = inv.busunit
where b.invoicedate between @StartDate and @EndDate
Group by busunitdesc) Tbl1

?????


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[chamilz]

if it is SQL 2005, use CTE

with Detail(empcount,busunitdesc)
as
(
select count (distinct employeeid), busunitdesc
from invinvoicedetail inv 
inner join invinvoiceheader b on b.invoiceid = inv.invoiceid 
inner join ssbusunit u on u.busunitid = inv.busunit
where b.invoicedate between @StartDate and @EndDate Group by busunitdesc
)
select empcount,
       busunitdesc, 
       (select sum(empcount) from Detail) TotEmpCount
from   Detail;