COUNT(CASE THEN (COUNT DISTINCT)) ? (SOLVED)

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)

COUNT(CASE THEN (COUNT DISTINCT)) ? (SOLVED)

Author

Topic

apnw7931
Starting Member

9 Posts

Posted - 2010-12-26 : 18:10:50

Hi,

I have problem to code what i want to.

Please find attached my table and also the result i want to obtain.

I'm not capable of having the two last columns of my table Resultats (Compte Unique Jan, Compte Unique Fev).

I would like to count the number of difféerent (unique) account for January, the same for February.

Hère is what i already have :


Select
	Produit,
	Count(Compte),
	Count(CASE Date WHEN '01/01/2010' THEN 1 ELSE 0 END) AS "Compte Janv",
 	Count(CASE Date WHEN '01/02/2010' THEN 1 ELSE 0 END) AS "Compte Fév",
        ?? AS "Comptes Unique Jan",
        ?? AS "Comptes Unique Fév"

FROM
	ma_table

Where
	Date Between '01/01/2010' AND '01/02/2010'

GROUP BY
	Produit

I'll need something like :

CASE Date WHEN '01/01/2010' THEN (Count Distinct Compte)

Could you please help me ?

SOLUTION BROUGHT BY nigelrivett

Count(distinct CASE Date WHEN '01/01/2010' THEN Compte ELSE null END) AS [Comptes Unique Jan],
Count(distinct CASE Date WHEN '01/02/2010' THEN Compte ELSE null END) AS [Comptes Unique Fév]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-26 : 20:54:29

[code]
Count(distinct CASE Date WHEN '01/01/2010' THEN 1 ELSE 0 END) AS "Compte Janv",
[/code]

KH
[spoiler]Time is always against us[/spoiler]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-26 : 22:12:33

sum(CASE Date WHEN '01/01/2010' THEN 1 ELSE 0 END) AS "Compte Janv",

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-27 : 00:06:04

I agree with Nigel that sum is a better choice, but you vcan do this with COUNT too.

count(CASE Date WHEN '01/01/2010' THEN 1 ELSE NULL END) AS [Compte Janv],

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

apnw7931
Starting Member

9 Posts

Posted - 2010-12-27 : 03:10:44

Thank you all of you, but you misunderstood my problem.
I need the two last columns (Compte Unique Janv and Compte Unique Fév).
I need to count the unique accounts of each product:

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 03:40:16

Maybe
Count(distinct CASE Date WHEN '01/01/2010' THEN Compte ELSE null END) AS [Comptes Unique Jan],

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

apnw7931
Starting Member

9 Posts

Posted - 2010-12-27 : 03:51:15

quote:
Originally posted by nigelrivett

Maybe
Count(distinct CASE Date WHEN '01/01/2010' THEN Compte ELSE null END) AS [Comptes Unique Jan]

Thanks a lot Niguel. That is exactly what i needed.
I wish you a nice day.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-27 : 08:16:05

quote:
Originally posted by khtan

Count(distinct CASE Date WHEN '01/01/2010' THEN 1 ELSE 0 END) AS "Compte Janv",

KH
[spoiler]Time is always against us[/spoiler]

Note that this will count everything. CASE WHEN is of no use

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 10:11:02

>> Note that this will count everything. CASE WHEN is of no use
Try it and you will probably see what distinct does.

Note the op has tried it and it solves his problem.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-28 : 02:51:12

quote:
Originally posted by nigelrivett

>> Note that this will count everything. CASE WHEN is of no use
Try it and you will probably see what distinct does.

Note the op has tried it and it solves his problem.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

FYI,


select Count(distinct CASE Date WHEN '01/01/2010' THEN 1 ELSE 0 END) "Compte Janv"
from
(
select GETDATE() as date
union all
select '01/01/2010' 
union all
select GETDATE()
) as t

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 05:28:31

Need to read the whole thread. That was discarded long ago.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources