Unique record counts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique record counts

Author

Topic

Jannette
Starting Member

26 Posts

Posted - 2011-09-07 : 05:13:36

When I run this query I get a record list of 20 records.

select distinct reference_week from paye_wtr where paye_id = 12033 and reference_year=2011

(There are 2 records for week 1)
=====================================================================

When I run the following query I get a record list of 19 records.

select distinct reference_week from paye_wtr where paye_id = 12033 and reference_year=2011
=====================================================================

However when I run the next query, I get a count of 20 records

select distinct count(reference_week) from paye_wtr where paye_id = 12033 and reference_year=2011
=====================================================================

I was expecting the final query to give me a count of 19, why is this not happening ?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 05:19:38

or getting distinct count use

select count(distinct reference_week) from paye_wtr where paye_id = 12033 and reference_year=2011

and not the one you used. you need to first take distinct and then count hence it should be inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2011-09-07 : 05:45:31

Thanks for that.
However I have the full query here, and the fields PWQ and PWA don't seem to be giving unique counts.
=====================================================================
Select a.Employer, '"'+a.CANDa+'"', c.PWQ, c.PWA, a.DaysAccd, b.DaysPaid, '"Y"', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
Case When a.CandType = 'P' Then '"N"' Else '"Y"' End,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

From
(Select Distinct
Case When (CANDIDATE.Branch_Id=3) Then '"OWWEEK"'
When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id=25) Then '"OMMTH"'
Else '"OMWEEK"' End as Employer,
CANDIDATE.Candidate_Code as Canda, Sum(Holiday_Days) as DaysAccd,
Case When CANDIDATE.Candidate_Type = 'C' Then 'U'
When CANDIDATE.Candidate_Type = 'S' Then 'C' Else 'P' End as CandType
From PAYE_WTR Inner Join PAYE on PAYE_WTR.Paye_Id = PAYE.Paye_Id
Inner Join PERSON on PAYE.Person_Id = Person.Person_Id
Inner Join CANDIDATE on PERSON.Person_Id = CANDIDATE.Person_Id
Where PAYE_WTR.Reference_Year=2011 and CANDIDATE.Active_YN = 'Y'
Group By CANDIDATE.Candidate_Code, CANDIDATE.Candidate_Type, CANDIDATE.Branch_Id, CANDIDATE.Division_Id) a
Left Join
(Select Distinct CANDIDATE.Candidate_Code as Candb, SUM(Case Code_Id When 3 then Item_Quantity Else 0 End) as DaysPaid
From PAYSLIP Inner Join PAYSLIP_ITEM on PAYSLIP.Payslip_Id = Payslip_Item.Payslip_Id
Inner Join PAYE ON PAYSLIP.PAYE_Id = PAYE.PAYE_Id
Inner Join PERSON ON PAYE.Person_Id = PERSON.Person_Id
Inner Join CANDIDATE ON PERSON.Person_Id = CANDIDATE.Person_Id
Where PAYSLIP_ITEM.Reference_Year=2011 and CANDIDATE.Active_YN = 'Y'
Group By CANDIDATE.Candidate_Code) b

On a.CANDa = b.CANDb
Left Join
(Select Distinct CANDIDATE.Candidate_Code as Candc, Count(Distinct WTR_Id)as PWQ, Count(Distinct WTR_Id) as PWA
From PAYE_WTR Inner Join PAYE on PAYE_WTR.Paye_Id = PAYE.Paye_Id
Inner Join PERSON on PAYE.Person_Id = Person.Person_Id
Inner Join CANDIDATE on PERSON.Person_Id = CANDIDATE.Person_Id
Where PAYE_WTR.Reference_Year=2011 and CANDIDATE.Active_YN = 'Y'
Group By CANDIDATE.Candidate_Code) c

On a.CANDa=c.CANDc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 07:07:07

we wont be able to suggest you why unless we see how data is present in your table. wold you mind giving some sample data from table and explain why its not giving distinct count?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2011-09-07 : 08:15:05

Data from file for one client as follows:

Holiday_Days WTR_Id
1.000000 1
0.472651 1
0.472651 2
0.472651 3
0.472651 4
0.354526 5
0.354526 6
0.590927 7
0.472651 8
0.472651 9
0.354526 10
0.472651 11
0.472651 14
0.472651 15
0.472651 16
0.472651 17

There are 16 records, however I would like the count to be 15 as there are 2 week 1 records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources