MS SQL Server :: Distinct Count With Over Giving An Error

Using Distinct And Count (*) Is Not Giving The Results Expected

Feb 12, 2008

I have 2 tables. Product, ProductCategory.
Product table consists of (productid, productname, producttypeid, createdate, portid)
productcategory consists of (productid, categoryid, productcategoryid, categoryid, prodroletypeid, createdate)
I want to write sql query to get the unique products. So the count should only give one value/resuls. But when I run the following query, I am getting more than one rows
select count(distinct categoryid) as uniquecat
from productcategory as pc, product as p
where pc.prodroletypeid in ('P', 'F')
and pc.createdate <= dateadd(d, 30, getdate())
and p.portid = 100
group by pc.productid

View 3 Replies View Related

Union Giving Me Error Msg About Distinct Use

Aug 30, 2007

when i try to run this following query, i get an error message:

Microsoft OLE DB Provider for SQL Server error '80040e14'

The ntext data type cannot be selected as DISTINCT because it is not comparable.

/resultados_termo.asp, line 176

the query:

select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade
like '%2004%' or ano like '%2004%' )

when i use UNION ALL, i get repeated rows. i need the select distinct on it.

please, help.

thanks in advance.

View 4 Replies View Related

I Get An Error When I Use COUNT(DISTINCT Colum_name)

Oct 14, 2007

hello,
i have a working stored procedure SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS 'NrOfComments'
FROM Comments WHERE PictureID = @PictureID
witch returns among others the number of comments for a picture
i need to select the number of distinct users who commented that user too, so i added this at SELECT statement, COUNT(DISTINCT UserName) AS 'Expr1'
i get that error:
"Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause."
what should i do, to select the number of distinct users who commented on a specific picture?
here are the table rows, if you need that
CommentID --- UserName --- PictureID --- DateCommented
please help me, thank you

View 5 Replies View Related

Analysis :: DAX For Distinct Count Of Related Column Without Returning Out Of Memory Error?

Aug 10, 2015

I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.

I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows

List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())

My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:

1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?

There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct
sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

View 9 Replies View Related

Using Count With Distinct

Oct 23, 2001

hi!
i am trying to get a count of 3 distinct values , one of them being a datetime - am running into errors - any suggestions are appreciated.

select count(distinct individualid + intakeseq + exitdate)from #temp

i am trying to do a distinct on individualid + intakeseq + exitdate,
& then get their count.

Thanks!

View 3 Replies View Related

Count From Distinct

Mar 14, 2007

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 helpings

View 4 Replies View Related

Count Distinct

Mar 23, 2007

hi all,

sometimes, when tables are poorly joined, we have exactly same rows as a result.. so we use distinct.. but how can we count(*) the distinct rows?

select distinct d.docrefid from tbljobDocuments d
left join tbljobitems j on j.docrefid=d.docrefid
where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'

will return me

docrefid
DC01C06027

but when i try to count(*) only the distinct record, it count all..

select distinct d.docrefid, count(*) from tbljobDocuments d
left join tbljobitems j on j.docrefid=d.docrefid
where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'
group by d.docrefid

will return me

DocrefId Count
DC01C060272

when i expect

DocrefId Count
DC01C060271

~~~Focus on problem, not solution~~~

View 3 Replies View Related

DISTINCT AND COUNT

Nov 9, 2007

Hi,

I have write down following query ..

select a.patientid, a.providerid
from PatientDrugList a
JOIN Admin..Loadinstance b on a.intLoadInstanceId = b.intLoadInstanceId
where a.MailReasonTypeId = -1
and b.SubClientId = 22
and b.StatusTypeId > 0

Now how could I get distinct patientid count and distinct providerid count

Reply soon

View 2 Replies View Related

Distinct Count

Jul 20, 2006

Hi

I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.

Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).

Can some body help?

Thanks in Advance!

Santhosh

View 4 Replies View Related

SELECTing A DISTINCT COUNT?

Nov 30, 2003

I have an SQL statement that looks like the following:

SELECT [Docs-Entities].entityID, entityName, COUNT([Docs-DocsEntities].filename) AS numDocs
FROM [Docs-Entities] LEFT JOIN [Docs-DocsEntities]
ON [Docs-Entities].entityID = [Docs-DocsEntities].entityID
GROUP BY [Docs-Entities].entityID, entityName

but the problem is that numDocs (the COUNT) is not returning a distinct count. In the DocEntities table, a particular document can actually have multiple entries with the same entityID so that produces inflated numbers for numDocs. But when I do a SELECT DISTINCT on a particular entityID, the results are less and don't match the numDocs number because I only need to list the document one time. This is not a huge issue, but it looks bad on my site.

Is there a way that I can make COUNT count distinctly?

Thanks for the help and I hope I worded that cllearly...

View 2 Replies View Related

COUNT(DISTINCT) With ROLLUP

Aug 17, 2005

I'm struggling to fin a way to use DISTINCT keyword with ROLLUP (or Cube).For example,SELECT employee_city, employee_country, COUNT(DISTINCT employee_name)FROM employeeGROUP BY employee_city, employee_country WITH ROLLUPthat query does not work.Is there a workaround?Thx.

View 2 Replies View Related

Distinct Count(fieldname)

Jan 28, 1999

hi, I have a table that contain(custname,ordno,ordchange,ordlocation,reaso n)
I want to determine the following:
how many orders per customer,how many orders changed per customer,how many order location per customer, what reason for change per customer,

here what I wrote as a query, tell me if I am right. I thank you for your help

select custname, COUNT(DISTINCT ordno) ,
COUNT(DISTINCT ordchange) ,
COUNT(DISTINCT ordlocation) ,
COUNT( reason)
from customers a, orders b
where a.custname =b.custname
group by custname

View 1 Replies View Related

SELECT COUNT Of DISTINCT

Jul 19, 2006

helloi want to do only one query for :SELECT DISTINCT Name FROM UsersSELECT COUNT(Name) AS Names FROM Users WHERE (Name LIKE 'xxx')something like :SELECT Name, COUNT(Name) AS Names FROM Users WHERE Name IN (SELECT DISTINCT Name FROM Users)i must get :Joe 23julie 17.....thank you

View 3 Replies View Related

Distinct Row Count In A Table.

Aug 30, 2006

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.

View 7 Replies View Related

Distinct Count Query

Dec 11, 2007

I have a table with following fields
tdate
custcode
prodcode

table is filled with full year data and i want following result

I want count of distinct custcode in every past three months.

for example
Result like this

month tjan tfeb tmar tapr tmay ..... tdec

prod1
prod2
.
.
prod5

And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required.
Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.

Can any1 help me please.

I am using MS SQL 2005 and above table is a big table (approx 10 million records)

Sham

View 13 Replies View Related

Count Distinct Records

May 15, 2008

how can i count in sql the number or records taht would be returned if i did

select distinct site,date from allrecords

View 1 Replies View Related

Distinct Count, But Rewritten With Sum(case..)

Mar 28, 2007

It doesn't seem possibly, but maybe?
Is there a way to have an expression be used, but also benefit from using distinct on a column?

I'm looking for something like:
sum(case when dtEntered > '1-1-2006' then 1 else 0 end)
but also encorporating somehow a distinct count on UserName. So a username showing twice would only count once, and this would only be counted if the record's dtEntered date was greater than Jan 1, 2006.

The reason I'm writing the statement that way is because there are 5 columns which aggregate data by different time periods.

If it's not possible, I will just end up joining to the table multiple times, putting the date filter in the where clause.

View 3 Replies View Related

Ms Sql Select Count Distinct Probrem

Jul 12, 2004

I am trying to get the below query to work and can't seem to get past this error. Not sure but everywhere I look I think my syntax is right. I am probably missing something obvious but I'm just back from vacation and trying to get in the swing of things. Any help would be appreciated.

Select Distinct(chadcd) as Adjustor, Count (Distinct chclno) as NumberOfCases, Count (Distinct chclno,chwkno)as NumberofClauses
from clmhdr
where chpddt = '20040630'
Group By chadcd
Order by Adjustor

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ',chwkno'.

View 1 Replies View Related

Using Count To Find # Of Distinct Entries

Apr 17, 2008

I need a query to return the number of distinct call_num(s) between two timestamps.

For example search between 2008-04-17 05:00:00.000 and
2008-04-19 05:00:00.000

Calls
----------
id_tag(unique id) | status | call_num | entry_id(timestamp)

1 HOLD 0123456789 2008-04-17 05:07:00.080
2 ONSCENE 012345679 2008-04-17 05:10:00.012
3 ENROUTE 321654987 2008-04-19 04:00:00.000

so the sample answer would be : 2

View 2 Replies View Related

Distinct Count Fall Under Unique ID

Feb 19, 2014

Aim- Need to count how many [FDMSAccountNo] fall under a unique parentid

This is my query

SELECT [FDMSAccountNo]
,ParentID
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878028271889'

Which produces the following, The number of fdmsaccountno under parentid is two. Its two because one of the fdmsaccountno is = to parentid

FDMSAccountNo ParentID
878028271889878028271889
878028272887878028271889
878028273885878028271889

Desired result

Parentid #_of_outlets
878028271889 2

View 5 Replies View Related

Count By Distinct Value When All Parts Not Null

Mar 12, 2007

Hello,

I'm fairly new to SQL as so am looking for help with an ad hoc query. The data in focusing on is from table named APs:

FileNo Type PartNo Completed
6 Northbound 1 03/03/2007
6 Northbound 2 NULL
6 Other 1 NULL
6 Other 2 NULL
20 Proof 0 19/07/2006
20 TCP 0 21/07/2006
24 40-Day 1 16/01/2006
24 40-Day 2 16/03/2006
24 Other 0 NULL
44 Northbound 1 16/01/2006
44 Northbound 2 16/06/2006
44 Northbound 3 16/12/2006
44 Northbound 4 01/01/2007

I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):

SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs
FROM APs
WHERE (completed IS NOT NULL)
GROUP BY [File]

Total count should be: 4

View 9 Replies View Related

Count Multiple Distinct Columns

Jul 20, 2005

I want to build query to return how many rows are in this query:select distinct c1, c2 from t1But SQL won't accept this syntax:select count (distinct c1, c2) from t1Does someone know how to count multiple distinct columns? Thanks.--Disclaimer: This post is solely an individual opinion and does not speak onbehalf of any organization.

View 3 Replies View Related

COUNT (Distinct Column) = 0 With GROUP BY

Mar 20, 2008

I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN

0 "

I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.

------------------------------------
SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek,
COUNT(DISTINCT UserID) AS NumberOfUser

FROM testUserLoginDuration
WHERE Archived = 0
GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3))
ORDER BY
CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7
END

--------------
returns

MON 6
TUE 3
WED 5
THU 3
FRI 2
SAT 1

View 4 Replies View Related

How To Conditionally Count Distinct Values?

Mar 17, 2008

Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.

departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...

I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.

My report would produce the following output considering the above dataset:

Merchandise Number of customers who used a coupon
--------------------------------------------------------------------------------------------------------------
Department 1 2

Class 1 2

Barcode 123 2
Barcode 789 1
Department 2 0

Class 7 0

Barcode 456 0

I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.

View 3 Replies View Related

Asking For Help With Distinct Count Within Rolling Period...

May 22, 2008

Thanks for taking the time to read my post. I greatly appreciate it!

What i'm trying to do is get a distinct count of account numbers within a rolling period. My actual take is rather large but i've created a smaller-like version below. Please reference this table.

Account
Date

1
1/1/08

2
1/2/08

3
1/2/08

2
2/8/08

4
2/9/08

1
2/15/08

1
3/5/08

5
3/6/08

4
3/9/08

3
3/10/08

1
4/1/08

5
4/9/08

2
4/15/08

3
4/26/08

1
5/3/08

2
5/15/08

3
5/29/08

6
5/30/08

Let's say i want to return distinct count of accounts within a 2-month rolling period meaning in February, i'd get a distinct count for accounts in January & February, then in March i'd get a distinct count for February & March, then in April i'd get it for March & April, and so on... my results table would like the table below:

Account
Month

3
1

4
2

5
3

5
4

5
5

I had asked this before but it was a summing equation and not a unique count. I've tried to play with the summing equation to kind of make it work, but i'm starting to get a headache. It's probably so simple!

Here's my previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2289509

View 8 Replies View Related

Analysis :: Distinct Count On Cube

Jun 12, 2006

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

View 17 Replies View Related

DB Design :: How To Get Distinct Count Of Values

Sep 21, 2015

I have the below query,

select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column

[Code] ....

o/p: is :

LastEnforcementState NumberOfComputers PComputers

Compliant 7056 91.54
Downloading update(s) 39 0.51

[code]....

I want to add these two rows information into single row,

Failed to Download Updates(s) 131(127+8) 1.66(1.65+0.1)

how to do this in my sql query?

View 12 Replies View Related

How To Get Group Wise Distinct Count

May 9, 2008

Hi,

I need to get the groupwise distinct count .
For eg. Lets assume we have group as Category and under this there are two childs Category1 and Category2.
I need the distinct count under Category1 and Category2 separately with the count resetting after Category1 and Category2.
Any help will be greatly appreciated.

Regards,
Ravi

View 4 Replies View Related

Employee Count By Two Distinct Indicators

Mar 11, 2008

This seems simple enough, I'm trying to get a count of employees by different indicators. First total by job code, second by Union_Code.

So I'm looking to count every one by distinct job codes inside a distinct Union_code first, then total the number for that distinct union.

Any help would be appreciated.

SELECT emp.job_code,
tbljob.job_title,
emp.union_code

FROM emp

INNER JOIN
empjob ON empjob.emp_id = emp.emp_id
AND empjob.company = emp.company
AND empjob.job_code_id = 'PRIM'

INNER JOIN
tbljob ON tbljob.job_code = empjob.job_code
AND tbljob.company = empjob.company

WHERE (emp.company = 'ABC')
ORDER BY emp.union_code, empjob.job_code

View 5 Replies View Related

Query Help, Get Count Of Names Having Distinct Letters

May 8, 2007

here is a query

Code:

select count(d) from (select distinct left(name,9) as d from bscs where region like 'n1' and name like 'Alc_%')

what i am trying to do is to get count of the names, having distinct letters in left 9 positions and some conditions too. How to do it?

View 5 Replies View Related

Count Of Distinct Rows Of Only Selected Columns?

Aug 9, 2012

I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.

i don't want to see the distinct rows, i just want a count of how many are in the table.

View 4 Replies View Related