distinct count problem!!! - 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

Development Tools

Reporting Services Development

distinct count problem!!!

Author

Topic

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-11-07 : 16:40:22

Hi,
I have a table that I need to do a distinct count on, but it's not working as I wanted.

Table contain number, month_of_service,client,$$

This is my code:
select number,month_of_service,
case when number is 1 then '1'
when number is 2 then '2',etc.etc.,
count(distinct client) as client,sum($$) as $$
from mytable
group by number,month_of_service,
case when number is 1 then '1'
when number is 2 then '2',etc.etc.

well that code works fine, but the problem is that it count to the client more than once because of the month_of_service. I'm trying to do a parameter, so I need the month_of_service in my code. Is there a way to have the client count as one only regardless of how many month?

Please help!!!!!!!!!!!!!!!!!

Thanks. HOpe i make it clear.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 18:09:28

Are you using SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-11-07 : 18:29:14

Yes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 18:35:10

Then you can try the new windowed functions.

E 12°55'05.63"
N 56°04'39.26"

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-11-07 : 18:50:09

How? Can you tell me? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 19:02:54

Can you provide some unambiguous sample data and expected output?

E 12°55'05.63"
N 56°04'39.26"

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-11-07 : 19:20:11

Ok.
I have date range from january 2008 to march of 2008.
In January 2008, there are 3 clients (Matt,Julie,Ken)
in february 2008, there are 5 clients (Debbie,Debbie,Debra,Sara,Matt)
in March 2008, there are 4 clients (Debbie,Julie,julie,Matt).
The result I have right now, which is not really what I want is,
january 2008-3 distinct clients
february 2008-4 distinct clients
march 2008-3 distinct clients.
this works fine for month by month, but if I combine those months together is will be 10. I want it to be 6 clients only.
Is this helpful?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 19:35:31

Oh, nothing more?

DECLARE	@Sample TABLE
	(
		MonthName VARCHAR(20),
		ClientName VARCHAR(20)
	)

INSERT	@Sample
SELECT	'January', 'Matt' UNION ALL
SELECT	'January', 'Julie' UNION ALL
SELECT	'January', 'Ken' UNION ALL
SELECT	'February', 'Debbie' UNION ALL
SELECT	'February', 'Debbie' UNION ALL
SELECT	'February', 'Debra' UNION ALL
SELECT	'February', 'Sara' UNION ALL
SELECT	'February', 'Matt' UNION ALL
SELECT	'March', 'Debbie' UNION ALL
SELECT	'March', 'Julie' UNION ALL
SELECT	'March', 'Julie' UNION ALL
SELECT	'March', 'Matt'

SELECT		COALESCE(MonthName, 'Total') AS MonthName,
		COUNT(DISTINCT ClientName) AS UniqueNames
FROM		@Sample
GROUP BY	MonthName
WITH ROLLUP

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 05:59:20

if you want this to do in reporting services also, you can do this by specifying the scope of grouping. thus if you're grouping by year and then by month. you can get distinct count for year by using
=CountDistinct(Fields!Client.value,"YearGroupNameHere")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources