December 27, 2010 at 1:17 am
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 ?
December 27, 2010 at 2:28 am
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.com
Follow me on
Twitter: @sqltwins
December 27, 2010 at 3:08 am
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
December 27, 2010 at 6:36 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy