How To Count Distinct Members And Keep Good Performance
Mar 26, 2007
Hi all,
I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.
I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.
I tried to create a calculated measure like this:
Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)
The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?
Anyone have an idea to do this in a better way ?
View 8 Replies
ADVERTISEMENT
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
Mar 26, 2001
Hello
Would you please point again on good book for admistration
Thanks
View 2 Replies
View Related
Apr 11, 2008
Hello,
I need to choose the OS, for my SQL Server Database where performance is important and will be used on a regular basis of two hours.
Should we go for 32 bit or 64 bit processor?
How much RAM should be used 2 or 4GB ?
Should we use SAN, RAID or local disk , which one of them and why?
I need to tell the owner by Monday.
thanks,
Priyanka
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Nov 30, 2007
We 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
View 3 Replies
View Related
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
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
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
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
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
Mar 23, 2006
We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code
SELECT DISTINCT email FROM Subscribers
to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).
I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.
What do you performance gurus think?
View 3 Replies
View Related
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
