Distinct Count Via SQLFiddle

Question

This seems weird: [SQL FIDDLE]

The two users are definitely different i.e 1<>2. So why is the distinct count saying 1 ?

Additionally, I don't know if this is an academic exercise, but I'd definitely focus on using appropriate data types (datetime). — swasheck, Jun 25 '12 at 20:37
@swasheck ?! not academic, wish life was, we've got a 1000GB warehouse that uses `DateKey INT`... it saves space using that type for that key — whytheq, Jun 26 '12 at 17:02
@AaronBertrand Typically by convention all the dimension IDs are ints. Whether they are anonymous or "smart" depends http://www.kimballgroup.com/html/designtipsPDF/DesignTips2006/KU85SmartDateKeysPartitionFactTables.pdf Prior to 2008, I would have agreed to use them instead of DATETIME. Now we have DATE, which is preferable. — Cade Roux, Jun 26 '12 at 17:53
@CadeRoux I'm curious why `INT` was preferred over `SMALLDATETIME`, even before SQL Server 2008. Same storage size but still allows for all of the date/time operations, automatic validation, etc. With an `INT` key you can shove `20120230` in there unless you have complex constraints or triggers - and you haven't gained anything over `2012-02-28` in a `SMALLDATETIME` column! — Aaron Bertrand, Jun 26 '12 at 17:57
@AaronBertrand my mistake Aaron; getting my Types mixed up... it was this recent [question of mine](http://stackoverflow.com/questions/11170677/should-datekey-yyyymmdd-be-char8) where I'd asked why we had used `INT` rather than `CHAR(8)` for the DateKey - the answer being size. But as you've pointed out my actual question _should_ have been "Why use `INT` when we could have used `SMALLDATETIME`?" — whytheq, Jun 26 '12 at 19:36

score 4 · Accepted Answer · answered Jun 25 '12 at 20:29

4

You want to use distinct [User]

User is a special word

http://sqlfiddle.com/#!3/b4130/20

answered Jun 25 '12 at 20:29

Paul Way

1,966
1
13
10

score 3 · Answer 2 · answered Jun 25 '12 at 20:27

3

user is a reserved keyword. If you run:

select DateKey, User from the_table

you'll see that user is not 1 or 2, but something like user_b1234. Like Lamak suggested, use [user] to refer to the column and not the system variable.

answered Jun 25 '12 at 20:27

Andomar

232,371
49
380
404

1

I guess `date` is a less reserved keyword, but I'd still enclose it in `[]` :) – Andomar Jun 25 '12 at 20:33

score 3 · Answer 3 · answered Jun 25 '12 at 20:49

Here's an answer with some explanation...

User is not only a reserved word. It's more like a system defined read-only global variable.

It evaluates to the name of the current database user. So every row will get the same value and count(distinct user) is 1.
User produces the same value as the system defined USER_NAME() function.

So if you want User to be treated as a column name, write [User] as others have suggested, and as you did in your create table statement.

score 0 · Answer 4 · edited Sep 10 '14 at 03:04

0

I think you want this:

select 
    COUNT(DateKey)
    ,count(distinct User) Cnt
from the_table
where Datekey = 20120103
group by 
    DateKey

http://sqlfiddle.com/#!3/b4130/14/0

edited Sep 10 '14 at 03:04

Jeffrey Kemp

59,135
14
106
158

answered Jun 25 '12 at 20:27

Martijn

11,964
12
50
96

Distinct Count Via SQLFiddle

4 Answers4