Count Distinct Records
May 15, 2008how can i count in sql the number or records taht would be returned if i did
select distinct site,date from allrecords
ADVERTISEMENT
Count Distinct In Large Table With 30M Records?
Jul 7, 2015I have a task to count distinct records in a big table with roughly 30M records, performance is an important factor. Query is to be written to calculate weekly stats, weekly record number could be as high as 1M.
The actual result is like:
ID Policy
350235744Credit Cards
350235744PCI
350235744PCI Audit
So the final number for this particular Policy is 3
I can write the query like:
select count(distinct Incident_id) policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 526254 and costs 11 seconds to complete
or a query like:
Select distinct Incident_id, policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 749687 and costs roughly 1 minute to complete.
Result is different from the two queries, I believe the later gives correct number. How can I count the distinct based on a combo?Considering the size of data, what is the best and most efficient way to run the stats calculation against over 30 different scenarios (different policies and alert types) and not timeout?
Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?
May 21, 2015I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.
declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,
[Code] ....
-- If Referral start date is between our requested dates
ref.Referral_Start_Date between @startdate and @enddate
OR
-- Include referrals which started before our requested date, but are still active during our date range.
(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,
[code]....
Count For Varchar Field - How To Get Distinct Count
Jul 3, 2013I 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'
Using Count With Distinct
Oct 23, 2001hi!
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!
Count From Distinct
Mar 14, 2007Hello
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
Count Distinct
Mar 23, 2007hi 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~~~
DISTINCT AND COUNT
Nov 9, 2007Hi,
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
Distinct Count
Jul 20, 2006Hi
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
SELECTing A DISTINCT COUNT?
Nov 30, 2003I 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...
COUNT(DISTINCT) With ROLLUP
Aug 17, 2005I'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 RelatedDistinct Count(fieldname)
Jan 28, 1999hi, 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
SELECT COUNT Of DISTINCT
Jul 19, 2006helloi 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 RelatedDistinct Row Count In A Table.
Aug 30, 2006Hi,
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.
Distinct Count Query
Dec 11, 2007I 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
I Get An Error When I Use COUNT(DISTINCT Colum_name)
Oct 14, 2007hello,
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
Distinct Count, But Rewritten With Sum(case..)
Mar 28, 2007It 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.
Ms Sql Select Count Distinct Probrem
Jul 12, 2004I 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'.
Using Count To Find # Of Distinct Entries
Apr 17, 2008I 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
Distinct Count Fall Under Unique ID
Feb 19, 2014Aim- 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
Count By Distinct Value When All Parts Not Null
Mar 12, 2007Hello,
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
Count Multiple Distinct Columns
Jul 20, 2005I 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 RelatedCOUNT (Distinct Column) = 0 With GROUP BY
Mar 20, 2008I 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
How To Conditionally Count Distinct Values?
Mar 17, 2008Here 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.
Distinct Count With Over Giving An Error
Nov 30, 2007We have a table of students with fields for the ID of the school district and their school name. For example:
DistrictID
StudentName
SchoolName
10001
John Smith
Washington Elementary
10001
Jane Smith
Lincoln Middle
10002
David White
Hill High
...
...
...
I want is a listing with the school district ID, the student name, and the number of schools in the district.
I believe the following statement should give me what I want:
SELECT DistrictID, StudentName, COUNT(DISTINCT SchoolName) OVER (PARTITION BY DistrictID)
FROM StudentData
However, I get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'distinct'.
If I take out the DISTINCT it runs fine, however it counts all the rows as expected, which is not what I want.
If I remove the OVER clause it runs fine, however, it counds the schools across all the districts, which is not what I want.
I can remove the OVER clause and use GROUP BY, but not while also retrieving StudentName.
The SQL 2005 Online Books don't seem to indicate that my statement is invalid. Here are the links just for reference:
Count: http://msdn2.microsoft.com/en-us/library/ms175997.aspx
Over: http://msdn2.microsoft.com/en-us/library/ms189461.aspx
Anyone have an idea one what I'm doing wrong or how to go about getting this information?
Thanks!
Matt Penner
Asking For Help With Distinct Count Within Rolling Period...
May 22, 2008Thanks 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
Analysis :: Distinct Count On Cube
Jun 12, 2006i 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 RelatedDB Design :: How To Get Distinct Count Of Values
Sep 21, 2015I 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?
How To Get Group Wise Distinct Count
May 9, 2008Hi,
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
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
Using Distinct And Count (*) Is Not Giving The Results Expected
Feb 12, 2008I 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
Query Help, Get Count Of Names Having Distinct Letters
May 8, 2007here 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?
Count Of Distinct Rows Of Only Selected Columns?
Aug 9, 2012I 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.