count multiple distinct columns

I want to build query to return how many rows are in this query:
select distinct c1, c2 from t1

But SQL won't accept this syntax:
select count (distinct c1, c2) from t1

Does someone know how to count multiple distinct columns? Thanks.

--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.One method is to use a derived table:

SELECT COUNT(*)
FROM (
SELECT DISTINCT c1, c2
FROM t1) AS t1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dean" <noreply@.fakeaddress.com> wrote in message
news:cd1o36$aho$1@.news01.intel.com...
> I want to build query to return how many rows are in this query:
> select distinct c1, c2 from t1
> But SQL won't accept this syntax:
> select count (distinct c1, c2) from t1
> Does someone know how to count multiple distinct columns? Thanks.
>
> --
> Disclaimer: This post is solely an individual opinion and does not speak
on
> behalf of any organization.|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:8D_Ic.1568
> One method is to use a derived table:
> SELECT COUNT(*)
> FROM (
> SELECT DISTINCT c1, c2
> FROM t1) AS t1

Thanks! I was trying
SELECT COUNT(*) FROM (SELECT DISTINCT c1, c2 FROM t1)
but it wouldn't work without the "AS t1" at the end.

--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.|||Try this out..

SELECT COUNT(*) FROM (select distinct c1, c2 from t1)T

count from distinct

Hello

for mS SQL 2000

with

SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region

I get 254 rows

but how can I get the COUNT only ?

something like

SELECT COUNT(SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region) AS CPT FROM Groups

i must get 254

thank you for helpingsChange query according to your requirement...

USE Pubs

select count (*) as row_count from (select type, pub_id from titles group by type, pub_id) t

select type, pub_id from titles group by type, pub_id|||it works perfetly

thanks a lot Rajesh Patel|||Que?

SELECT COUNT(DISTINCT Name, Region)
FROM Groups

Doesn't work?

EDIT: No it doesn't...but

USE Northwind
GO
SELECT COUNT(DISTINCT CustomerID+CONVERT(varchar(10),EmployeeID)) FROM Orders

Does|||i'm liking rajesh's solution way more, brett ;)sql

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 distinct

can you set up a measure with "countdistinct" where some column in fact tabl
e
<> 0
i want a count of members in my fact table where total_bal <> 0 by all
dimensions
does someone have an exampleYou can create a separate cube with a "Distinct Count" measure for the
member column, and set the "Source Table Filter" for the cube to
"total_bal <> 0". This cube can be combined with existing cubes in a
virtual cube.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I have cube with staff dimension which has staff_member_id as a level
Cube has metrics for example total_bal
I want to count distinct staff_member id's for each level in my time dimensi
on
daily, weekly, monthly etc where total_bal > 0
Is this possible to do within an MDX, can you send me an example of
something like this
Creating separate cubes would mean we have to create atleast 25 cubes as we
have 25 different measures we want to calculate like above
Thanks
"Deepak Puri" wrote:

> You can create a separate cube with a "Distinct Count" measure for the
> member column, and set the "Source Table Filter" for the cube to
> "total_bal <> 0". This cube can be combined with existing cubes in a
> virtual cube.
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Assuming that there is a [tot_bal] "sum" measure, based on a "tot_bal"
fact table field, and that this field is always >= 0, MDX can be used to
count [staff_member_id] members for a given node in the time hierarchy:
[vbcol=seagreen]
With Member [Measures].[StaffCount] as
'Count(Filter(NonEmptyCrossJoin(
[Staff].[staff_member_id].Members,
{[Time].CurrentMember}, 1),
[Measures].[tot_bal] > 0))'[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||this is my exact MDX statement
i have result set out oof this MDX like this in columns which separate WTD,
MTD etc
MEMBERCAPTION thisdate wktd monthtd lastmonth ytd
1008 21 43 90 100 8
97
StaffCount 12 23 12 13 88
8
Can you help me write Measures.Staffcount please..
select 1 as col1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'aggregate(time.[20040430]:time.&#
91;20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[2
0040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):tim
e.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.
1;20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20
040430])'
-- Member [Measures].[StaffCount] as ''
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
select { [measures].[StaffCount] , [measures].[1008] }
on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])
")
"Deepak Puri" wrote:

> Assuming that there is a [tot_bal] "sum" measure, based on a "tot_bal"
> fact table field, and that this field is always >= 0, MDX can be used to
> count [staff_member_id] members for a given node in the time hierarchy
:
>
> With Member [Measures].[StaffCount] as
> 'Count(Filter(NonEmptyCrossJoin(
> [Staff].[staff_member_id].Members,
> {[Time].CurrentMember}, 1),
> [Measures].[tot_bal] > 0))'
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||To count staff where [Measures].[1008] > 0:
[vbcol=seagreen]
With Member [Measures].[StaffCount] as
'Count(Filter(
[Staff].[staff_member_id].Members,
[Measures].[1008] > 0))'[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I tied this and it makes the MX extremely slow, 50 secs instead of 2 secs
which it was taking earlier
is there any way to speed this up or some other mechanism
"Deepak Puri" wrote:

> To count staff where [Measures].[1008] > 0:
>
> With Member [Measures].[StaffCount] as
> 'Count(Filter(
> [Staff].[staff_member_id].Members,
> [Measures].[1008] > 0))'
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||You could try AS 2005 (Yukon), which supports aggregation of distinct
count measures.
If the different time ranges are defined in Named Sets (like [YTDSet]),
then staff count for each set could be optimized by NonEmptyCrossJoin()
pre-filtering.
Short of these options, you could try optimizing the longer time ranges,
using the approach discussed in this thread, where Chris Webb discusses
the possibility to optimize a time series, and I posted some sample MDX
code. You can refer to Chris's BI Blog (referenced in this thread) for
more details:
http://groups-beta.google.com/_grou...serv_er.olap/m.
.
Newsgroups: microsoft.public.sqlserver.ola_p
Subject: RE: Problem with MDX query
Chris Webb Dec 31 2004, 4:01 am
- Hide quoted text -
- Show quoted text -
I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.
Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/__cwebbbi/Blog/cns!1pi7ETChsJ1un___2s41jm9
I.|||Deepak
I could not go to the link and am not an expret an MDX to understand your
code so easily
can you pls re-write this according to your recomendation so it can improve
in speed
THANKS!!!!
select 1 as col1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'aggregate(time.[20040430]:time.&#
91;20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[2
0040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):tim
e.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.
1;20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20
040430])'
Member [Measures].[StaffCount] as
'Count(Filter([Staff].[agent].Members,[Measures].[CAS_IC_C]
> 0))'
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[1008], [Measures].[StaffCount] }
on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])")|||You should really work this out yourself, since I don't have your cube
to test against, but something like:
[vbcol=seagreen]
select 1 as col1, * from openquery (ROP_OLAP, "
with
set [ThisDateSet] as
'{time.[20040430]:time.[20040430]}'
set [WktdSet] as
'{time.[20040426]:time.[20040430]}'
set [MonthdSet] as
'{openingperiod([day],ancestor([20040430],[month])):time.&#
91;20040430]}'
set [LastMonthSet] as
'{time.[20040301]:time.[20040331]}'
set [YTDSet] as
'{time.[20040301]:time.[20040430]}'
Member [Measures].[InMonthd] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[MonthdSet]).Count
=
0'
Member [Measures].[InLastMonth] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[LastMonthSet]).Co
unt
= 0'
Member [Measures].[InYTD] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[YTDSet]).Count =
0'
Set [MonthdOpt] as 'Filter([Time].Members, [Measures].[InMon
thd]
AND Not ( [Measures].[InMonthd], [Time].Parent))'
Set [LastMonthOpt] as 'Filter([Time].Members, [Measures].[In
LastMonth]
AND Not ( [Measures].[InLastMonth], [Time].Parent))'
Set [YTDOpt] as 'Filter([Time].Members, [Measures].[InYTD]
AND Not ( [Measures].[InYTD], [Time].Parent))'
member [time].[thisdate] as 'aggregate([WktdSet])'
member [time].[wktd] as 'aggregate([WktdSet])'
member [time].[monthtd] as 'aggregate([MonthdOpt])'
member [time].[lastmonth] as 'aggregate([LastMonthOpt])'
member [time].[ytd] as 'aggregate([YTDOpt])'
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
Member [Measures].[StaffCount] as
'Count(Filter(NonEmptyCrossJoin(
[Staff].[staff_member_id].Members),
[Measures].[CAS_IC_C] > 0))'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[1008], [Measures].[StaffCount]
} on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])")[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

count distinct

Hi... hereby i hv a problem

At my database, I hv a field "component_detail_key",
and the data is :
T002811_1
T002811_2
T002812_1
T002812_2
T002813_1
T002813_2

I get the data before _ :
T002811
T002811
T002812
T002812
T002813
T002813

Now I hv to count distinct, the output should be 3.
I use 2 for loop in this function but can't do that.
Below is my coding :

int distinct = 0;

for (int i = 1; i < a; i++){
for (int j = 1; j < a; j++)
{
if (swkey[i]==swkey[j])
{
distinct = distinct;
} else {
distinct ++;
}
} // End for
}

The answer is :5 , 10 , 15 , 20 , 25 , 30

Anyone can help?pls~Why don't you just do this in SQL something like this:

SELECT COUNT( DISTINCT( SUBSTR( code, 1, INSTR(code,'_')-1 )))
FROM table
...;

I'm using Oracle SUBSTR and INSTR functions, but most DBMSs have something similar.|||May I know what is the meaning for :INSTR(code,'_')-1?
The "code" is field or...?

Thx~|||INSTR(string1, string2) returns the position of string2 within string1. Therefore, SUBSTR(string1, 1, INSTR(string1, string2)-1) will return a new string containing all the chacracters of string1, prior to the occurence of string2.

Example,

String1('123456');
String2('4');

SUBSTR(string1, 1, INSTR(string1, string2)-1) = "123"|||oh i c~
Thanks very much!!!|||BUt still hv error : invalid command for INSTR...
I am using jsp... is it can't support this code?|||I would recommend that the DBMS perform the complete operation.

Java:
IndexOf(string)|||sorry not understand this.....
I am fresh with Java

is it
String sqlsw = "SELECT SUBSTRING_INDEX(component_detail_key, '_', 1) AS sw FROM component_detail WHERE component_key=?";
?
but still can't...|||I solve the problem by CHARINDEX,
thx very much for your help :)

Count (Distinct ?

Count(Distinct col1) only works with one column. What you can do is
something like this:
insert @.Table values('Jeff', 'Jones')
insert @.Table values('Jeff', 'Jones')
insert @.Table values('Jeff', 'James')
insert @.Table values('Ed', 'James')
insert @.Table values('Ed', 'James')
select Count (Distinct FName+'|'+LName)
from @.Table
Note that the I just didn't concatenate Fname and Lname because 'Tom' +
'aster' is not the same at 'To' + 'Master'> select Count (Distinct FName+'|'+LName)
Careful, make sure neither is NULLable.

Count (Distinct ?

SQL Server 2000
Why can't I do this:
Count (Distinct FName, LName)
?
TIA,
OwenWhat are you trying to do? COUNT returns a single value. So if you have 15
distinct first names and 18 distinct last names, what result do you expect
from your query?
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:uPaNUYHRGHA.2436@.TK2MSFTNGP11.phx.gbl...
> SQL Server 2000
> Why can't I do this:
> Count (Distinct FName, LName)
> ?
> TIA,
> Owen
>

Count (Distinct ?

Owen Mortensen wrote:
> SQL Server 2000
> Why can't I do this:
> Count (Distinct FName, LName)
> ?
> TIA,
> Owen
Do:
SELECT COUNT(*)
FROM
(SELECT DISTINCT fname, lname
FROM some_table
WHERE fname IS NOT NULL
AND lname IS NOT NULL) AS T;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--AHHH! That will do it!
Thanks!
Owen
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142019969.039573.5400@.v46g2000cwv.googlegroups.com...
> Owen Mortensen wrote:
> Do:
> SELECT COUNT(*)
> FROM
> (SELECT DISTINCT fname, lname
> FROM some_table
> WHERE fname IS NOT NULL
> AND lname IS NOT NULL) AS T;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

COUNT (DISTINCT (myfield)) PROBLEM

it works well
SELECT DISTINCT ([cinsiyet]) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
but i need to get recordcount of this query whis oledb.datareader doesnt say
it to me...
SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
it returns error ? how can i get this query resultset ?> it returns error ? how can i get this query resultset ?
What error? Are you referring to the column name in your .NET code? If so,
you'll need to add an alias, and refer to that.
SELECT Count_cinsiyet = COUNT(DISTINCT cinsiyet)
FROM URUNLER
WHERE cinsiyet NOT IN
('Bileklik' , 'Yuzuk' , 'Set' , 'Kupe' , 'Bilezik' , 'Kolye');
Now, refer to reader["Count_cinsiyet"];
Or, refer to the ordinal number instead of the name.|||it is an query error.. my db is an access database.. I tried it as an access
query in access it returns error ?
"Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
> it works well
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> but i need to get recordcount of this query whis oledb.datareader doesnt
> say it to me...
> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> it returns error ? how can i get this query resultset ?
>|||> it is an query error.
Can you be MORE SPECIFIC?

> query in access it returns error ?
Can you be MORE SPECIFIC?|||it works well in SQL Database.. But when i try it in access it returns
error. it says there is missing operator in this query ?
how can i solve it ?
"Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
> it works well
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> but i need to get recordcount of this query whis oledb.datareader doesnt
> say it to me...
> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
> it returns error ? how can i get this query resultset ?
>|||> it works well in SQL Database.. But when i try it in access it returns
> error.
Egads. WHAT ERROR?|||I'm not 100% sure, but I don't think Access supports Count(Distinct())
HTH
Dan Artuso
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:O4wbFiO$FHA.208@.tk2msftngp13.phx.gbl...
> it works well in SQL Database.. But when i try it in access it returns
> error. it says there is missing operator in this query ?
> how can i solve it ?
>
> "Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
> yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
>|||> I'm not 100% sure, but I don't think Access supports Count(Distinct())
It sure would be nice to see the actual error message. I'm not holding my
breath...|||Aaron Bertrand [SQL Server MVP] wrote:
> It sure would be nice to see the actual error message. I'm not
> holding my breath...
He did say "missing operator" ... :-)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Dan is correct: JetSQL does not support COUNT(DISTINCT ...)
What you have to do is use a subquery (aka derived table):
Select Count(*) From
(SELECT DISTINCT ([cinsiyet]) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye') As q
Bob Barrows
Savas Ates wrote:
> it works well in SQL Database.. But when i try it in access it returns
> error. it says there is missing operator in this query ?
> how can i solve it ?
>
> "Savas Ates" <savas@.indexinteractive.com>, haber iletisinde unlar
> yazd:%23cB1tMO$FHA.1568@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"sql

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)