COUNT(CASE THEN (COUNT DISTINCT)) ?
-
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 different (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 ?
-
You can try to use CTE (Common Table Expressions).
Can you post the INSERT statements for your table so that it becomes easy for us to come up with a properly tested solution?
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.comFollow me on
Twitter: @sqltwins -
Try this query:
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",
max(unique_count_jan) as jan_unique,
max(unique_count_feb) as feb_unique
FROM
ma_table T
cross apply(select count(distinct(Compte)) as unique_count_jan from ma_table P where P.prod = T.prod and MONTH(P.Date)=1) janU
cross apply(select count(distinct(Compte)) as unique_count_feb from ma_table P where P.prod = T.prod and MONTH(P.Date)=2) febU
Where
Date Between '01/01/2010' AND '01/02/2010'
GROUP BY
Produit
-
Here is the solution of my problem
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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply