Count Distinct

Hello,

I was wondering does count(distinct) work on multiiple columns? I get syntax error on "," with the following code.

select count(distinct cookie1, cookie2) from BookersLookers_DataSet

Thanks,

-Lawrence

You cant pass more than one column in COUNT function..

Use the following query...

Code Snippet

Select Count(*) From

(Select cookie1,cookie2 From BookersLookers_DataSet Group By cookie1,cookie2) as Data

--Or

Select Count(*) From

(Select Distinct cookie1,cookie2 From BookersLookers_DataSet) as Data

|||

Actually, to get the count(), you need to use a GROUP BY, and if you use a GROUP BY, then DISTINCT is not necessary (redundent).

Code Snippet

SELECT
Cookie1,
Cookie2,
[Count] = count(1)
FROM BookersLookers_Dataset
GROUP BY
Cookie1,
Cookie2

count and update syntax help

Field Names: NOs Code Code1a UniqueID
61 10 888 10
62 10 888 11
63 10 888 12

Logic: If Count(code >1) & Count (Code1a >1)
Update the (Nos) to EQUAL the same Value.
ALL the Nos for the above examble should be the same value for
all three records whether it's 61 for all three records of any
of the other two numbers, it doesn't matter as long as the equal the same value.
How can this be done via sql?Hi!
I' didn't really understood what you mean, but I'm sure you can use:

select code from tabx group by code having count(*)>1
select min(NOs) from tabx where ....
/Bjrn

"Spencer" <spencey@.mindspring.com> wrote in message
news:673a4d41.0311120525.3203e609@.posting.google.c om...
> Field Names: NOs Code Code1a
UniqueID
> 61 10 888
10
> 62 10 888
11
> 63 10 888
12
> Logic: If Count(code >1) & Count (Code1a >1)
> Update the (Nos) to EQUAL the same Value.
> ALL the Nos for the above examble should be the same value for
> all three records whether it's 61 for all three records of any
> of the other two numbers, it doesn't matter as long as the equal the same
value.
> How can this be done via sql?|||Replied in microsoft.public.sqlserver.programming:

> UPDATE Sometable
> SET nos =
> (SELECT MIN(nos)
> FROM Sometable AS S
> WHERE S.code = Sometable.code
> AND S.code1a = Sometable.code1a)

Please don't multi-post.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi Spencer,

Here's one way of doing it using UPDATE FROM. - Louis

create table #T (n int, codeA int, codeB int, id uniqueidentifier)
insert into #T values(11,1,888,newid())
insert into #T values(12,1,888,newid())
insert into #T values(13,1,888,newid())
insert into #T values(21,10,888,newid())
insert into #T values(22,10,888,newid())
insert into #T values(23,10,888,newid())
insert into #T values(1,1,111,newid())
insert into #T values(2,2,222,newid())
insert into #T values(3,3,333,newid())
insert into #T values(3,4,444,newid())

select codeA,codeB,n=min(n)
into #U
from #T
group by codeA,codeB
having count(*)>1

update #T
set n=b.n
from #T as a
JOIN #U as b
ON a.codeA=b.codeA and a.codeB=b.codeB

select n,codeA,codeB from #T

returns:
n codeA codeB
---- ---- ----
11 1 888
11 1 888
11 1 888
21 10 888
21 10 888
21 10 888
1 1 111
2 2 222
3 3 333
3 4 444|||Wow! Do you have something against using an UPDATE subquery?

It may be worth adding a WHERE clause to my original suggestion in line with
the HAVING COUNT(*)>1 requirement.

UPDATE Sometable
SET nos =
(SELECT MIN(nos)
FROM Sometable AS S
WHERE S.code = Sometable.code
AND S.code1a = Sometable.code1a)
WHERE nos >
(SELECT MIN(nos)
FROM Sometable AS S
WHERE S.code = Sometable.code
AND S.code1a = Sometable.code1a)

Assuming Nos is not nullable.

--
David Portas
----
Please reply only to the newsgroup
--|||> Wow! Do you have something against using an UPDATE subquery?
Hi David,

My little brain can't handle subqueries ;) I'm currently undergoing
brain overload, trying to figure out how to use W3C SVG to create
dynamic charts on the web.

Count ( Distinct Case ..) syntax error

I am getting a syntax error for the following piece of code:

Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)

I don't understand what's wrong with it. I came across constructs like

Select Count(Distinct Case When ...Then ID End ) ...

and also

Select Distinct Count(Case When ...)...

What I want is the first one, a count of the distinct IDs.

What am I missing?

Magic:

I am not sure what you are getting; could you post your error message? When I run what follows it compiles OK and seems to run OK:

Code Snippet

select Count(Distinct Case When (StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0)
and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52)
Then ProjectID end
) as distinctCount
from ( select 1 as startDate, 1 as ProjectStatusId, 1 as projectId) x

/*
distinctCount
-
0

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.
*/

|||My code is below. I can't use the insert code feature because whenever I click on that it makes my whole post dissappear. I'm using Firefox.

The error is:
Incorrect syntax near 'distinct'.

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End) Over (Partition By DepartmentDetails.DepartmentName, ProjectCategory) [NewRequests]

From #tempResourceAllocation
Inner join dbo.DepartmentDetails
On (#tempResourceAllocation.ParentDepartmentID = DepartmentDetails.DepartmentID)

Order By ProjectCategory

|||

The problem seems to be when using "distinct" inside an aggregate function and the "over" clause. I haven't be able to find anything related to this in BOL.

AMB

|||

I fully agree with AMB; however, I was able to get my version to work using GROUP BY instead of OVER. Give GROUP BY a try instead. What I have looks like this:

Code Snippet

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)
as [NewRequests]
from ( select 1 as startDate,
1 as projectStatusId,
1 as departmentName,
1 as projectCategory,
1 as projectId
) as departmentDetails
group by DepartmentDetails.DepartmentName, ProjectCategory
order by ProjectCategory

/*
DepartmentName ProjectCategory NewRequests
-- --
1 1 0
*/

|||

I also see a potential problem with your date range. It looks to me like you have the same TO and FROM date if you are trying to get data from the previous month, change

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))

to

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate()), 0))

|||Yes, thanks, I was playing around getting some values from the previous one month intervals and when I switched it back I missed that.

Thanks for the input everyone.

I don't really know what to do since I was using Partition Over as another way to create subtotals based on the category but only for a subset of the columns in the table. I tried with Rollup but I couldn't get this functionality because it forces me to put all the columns in Group By and it messes up my layout giving me summary totals based on different criteria rather than solely on the Category field.

Even so Rollup doesn't work with Distinct aggregates which is a problem because I do have several entries with the same key of interest in my table just because in someother column I have distinct values for the same key and counting will include duplicates also.

|||

hi, did you try this?

Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID else 0 End)

|||Whether it counts a 1 or a 0 isn't the result still one? As in one item that got counted?
|||

I think Tolga has a good point NULL does not help you towards a distinct count. Notice for this two-record select that that one of the entries is null. Also, note that the count is "1" and not "2":

Code Snippet

select count(distinct what) as theCount from (
select 1 as what union select null
) a

/*
theCount
--
1
*/

|||

yes you are right, result still one,

okey, try to sum,

sum(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then 1 else 0 End)