| Author |
 Topic  |
|
|
apnw7931
Starting Member
9 Posts |
 Posted - 12/26/2010 : 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]
|
Edited by - apnw7931 on 12/27/2010 03:52:35
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 12/26/2010 : 20:54:29
|
Count(distinct CASE Date WHEN '01/01/2010' THEN 1 ELSE 0 END) AS "Compte Janv",
KH
Time is always against us
|
 |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/26/2010 : 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
Sweden
29156 Posts |
Posted - 12/27/2010 : 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 - 12/27/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/27/2010 : 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 - 12/27/2010 : 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
India
22469 Posts |
Posted - 12/27/2010 : 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
Time is always against us
Note that this will count everything. CASE WHEN is of no use 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/27/2010 : 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
India
22469 Posts |
Posted - 12/28/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/28/2010 : 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. |
|
|
| |
Topic |
|
COUNT(CASE THEN (COUNT DISTINCT)) ? (SOLVED)
